Solved

loop through and assign variables using mysql bind without knowing table column names

Posted on 2015-01-23
8
96 Views
Last Modified: 2015-02-01
Hi

I would like to be able to loop through all the results returned from the following SELECT query and set the variables to match the values if possible, how can that be achieved please?

For example within the loop if the colum iTitle is returned set the variable to be that value  e.g. $iTitle = '3 piece sofa';

There is just a cut down SELECT list here but this could be quite a large or changing set of values.

Happy to hear other better ways of do this from you gurus if this is not ideal :)

$db = new mysqli(_DBHOST,_DBUSER,_DBPASSWORD,_DBTABLE);
if($db->connect_errno > 0) {die('Unable to connect to database [' . $db->connect_error . ']'); } 	

$stmt = $db->prepare("SELECT iID,iSellerID,iCategory,iTitle,iCondition,iImages,iDescription,iPrice FROM item WHERE iID = " . $itemID );
$stmt->execute(); 

$stmt->bind_result($iID,$iSellerID,$iCategory,$iTitle,$iCondition,$iImages,$iDescription,$iPrice);
$stmt->store_result();

$count = $stmt->num_rows();

while ($stmt->fetch()) {
  // set all variables here with the same name
  e.g. $$iTitle = $iTitle;
}

$stmt->close();							
$db->close();

Open in new window

0
Comment
Question by:Neil Thompson
  • 5
  • 3
8 Comments
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 40567473
Since there is no LIMIT clause here, how many rows do you expect to get back from this query?
0
 
LVL 3

Author Comment

by:Neil Thompson
ID: 40567550
Hi Ray, Only 1 row based on the iID that matches the one supplied ( and sanitised etc)

Apologies, should have said the item Id (iID) is a primary key so unique. So this should only return 0 or 1 row back
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 40567607
That's good news.  Just for your own documentation, you might want to limit the query with LIMIT 1.
0
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 109

Expert Comment

by:Ray Paseur
ID: 40567624
I don't really know if this works - you may want to experiment with the process to see exactly what you get back from the query.  But it's a good enough starting point.  One possibility might be that you would need to define the variables before you can bind them to the result.  That's not certain, but it could be one of the idiosyncrasies of PHP.

$db = new mysqli(_DBHOST,_DBUSER,_DBPASSWORD,_DBTABLE);
if($db->connect_errno > 0) {die('Unable to connect to database [' . $db->connect_error . ']'); } 	

$stmt = $db->prepare("SELECT iID,iSellerID,iCategory,iTitle,iCondition,iImages,iDescription,iPrice FROM item WHERE iID = " . $itemID . 'LIMIT 1' );
$stmt->execute(); 

$stmt->bind_result($iID,$iSellerID,$iCategory,$iTitle,$iCondition,$iImages,$iDescription,$iPrice);
$stmt->fetch();

var_dump($iID,$iSellerID,$iCategory,$iTitle,$iCondition,$iImages,$iDescription,$iPrice);

Open in new window

0
 
LVL 3

Author Comment

by:Neil Thompson
ID: 40582487
Apologies for the delay in replying Ray, just back from a holiday.

What if I'm unsure about the columns in the database in the first place? I'm kind of looking forward thinking things may change and if I set the fields specifically, then alter / add one I would need to amend all my code.

With your experience can you see any better ways round a situation like this. Should I perhaps be doing a select * with certain pre determined limitations (WHERE, AND, LIMIT  etc) and then return as an object to use at will?

Regards
Neil
0
 
LVL 109

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 40582535
... if I set the fields specifically, then alter / add one I would need to amend all my code.
That's always a risk when you're writing your own code - requirements can change and that means code can change.  There are few ways around this issue, but you can reduce the impact with some programming generalizations.

1. Write your own database abstraction layer.  Your PHP script can extend the MySQLI class.  You can use queries that get the names of the columns  so your data bindings can adapt to the exact structure of the table.  You can use some kind of adapter pattern to translate the column names into variable names.

2. Use an existing, general-purpose abstraction layer, perhaps an ORM like Eloquent that gives you an active-record pattern.

BTW, this is funny:
http://www.yegor256.com/2014/12/01/orm-offensive-anti-pattern.html
0
 
LVL 3

Author Closing Comment

by:Neil Thompson
ID: 40582635
Many thanks Ray

As always a great answer with lots of relevant information. It always makes me step back and re-focus to implement better solutions going forward.

:)
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 40582665
Thanks for the points -- it's a great question! ~Ray
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question