Solved

using a php class to do a database search but return an array back

Posted on 2013-06-14
3
310 Views
Last Modified: 2013-06-14
Hi

I have the folling code in one of my classes. its intention is to take a SQL query and pass back an array of results for me to then use alse where, it doesnt seem to do this though, it seems to only send back 1 row?

	
public function executeQuery($query) {
				
      $db = new mysqli(_DBHOST, _DBUSER, _DBPASSWORD, _DBTABLE);
		
      if ($result = $db->query($query)) {	
	$row = $result->fetch_assoc();	
	$result->close();	
	$db->close();	
	return $row;
     }
		
}	

Open in new window



im calling it using :

$searchResultsRS = $db->executeQuery($itemSearchSql);

Open in new window



then, i wanted to pass this record (or records) to a template like this:

while ($row = mysqli_fetch_array($searchResultsRS)) {
      $items[] = array(
            'iID' => $row['iID'],
            'iCategory' => $row['iCategory'],
            'cName' => $row['cName']
                                      );
)



any ideas or tips to do this better please?
0
Comment
Question by:NeilT
  • 2
3 Comments
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 39247560
The first code snippet will only return one row.  If you want to get the entire results set, you would use an iterator like while() or maybe use PDO.  This article has parallel examples in different MySQL extensions.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

If you want, please post the rest of the class and maybe we can come up with something that will make more sense than just this function.  It's missing a lot of things it needs, like error handling.
0
 
LVL 3

Author Closing Comment

by:NeilT
ID: 39247907
Thanks Ray

I've fudged it to work at present as it was actually another search i needed to quickly demo.

Your tutorial is excellent so I'm now going to take a step back and have a good read and learn.

Are you aware of a good class that I can use to add/edit/delete/read from a database with all the great error handling etc build in?

Regards
Neil
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39248036
Thanks for the points.  I'm not aware of any database class.  I tend to write web services (rather than web sites) and as a result of needing ultra-high performance I usually write and optimize my own code.  I'm sure there is something that offers a generalized solution - I just don't know what it is!

Best regards, ~Ray
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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…
The viewer will learn how to count occurrences of each item in an array.

929 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

11 Experts available now in Live!

Get 1:1 Help Now