Solved

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

Posted on 2015-01-23
8
98 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 110

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 110

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
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!

 
LVL 110

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 110

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 110

Expert Comment

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

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This article discusses how to implement server side field validation and display customized error messages to the client.
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…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

751 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