Solved

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

Posted on 2015-01-23
8
94 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:NeilT
  • 5
  • 3
8 Comments
 
LVL 108

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:NeilT
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 108

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
 
LVL 108

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 3

Author Comment

by:NeilT
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 108

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:NeilT
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 108

Expert Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

910 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now