Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2015-01-23
8
Medium Priority
?
100 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 111

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 111

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 111

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 111

Accepted Solution

by:
Ray Paseur earned 2000 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 111

Expert Comment

by:Ray Paseur
ID: 40582665
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!

Question has a verified solution.

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

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
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…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

722 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