Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 101
  • Last Modified:

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

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
Neil Thompson
Asked:
Neil Thompson
  • 5
  • 3
1 Solution
 
Ray PaseurCommented:
Since there is no LIMIT clause here, how many rows do you expect to get back from this query?
0
 
Neil ThompsonSenior Systems DeveloperAuthor Commented:
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
 
Ray PaseurCommented:
That's good news.  Just for your own documentation, you might want to limit the query with LIMIT 1.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Ray PaseurCommented:
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
 
Neil ThompsonSenior Systems DeveloperAuthor Commented:
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
 
Ray PaseurCommented:
... 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
 
Neil ThompsonSenior Systems DeveloperAuthor Commented:
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
 
Ray PaseurCommented:
Thanks for the points -- it's a great question! ~Ray
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now