Using Php To Access Multiple Tables From Mysql

[ssp2010 0]

Hi guys im new to php and mysql and have a problem that the book im using and online manual doesn't seem to help me with or I might be overlooking it. Here's the deal I've created a few tables (acura,honda,lexus etc.....)under a datatbase called "cars" . under the tables are categories such as userid,make and model , and a description . I've created a search form with drop down selects and a search box to search the database. What I cant get accomplished is when a user clicks on a automobile it does not show the results from the database. I've tried many ways to figure this out and read alot of forums and still have no luck. I want a user to be able to select a maker "honda" or any other and be able to find everything that is in the honda table or if they want to be more specific such as search for a "04 civic" under honda they can do so by typing it in the search box. Can someone please show me the easiest way to do this by showing how its done exactly from the html form to the php script? I'd greatly appreciate it. thanks

Edited March 19, 2010 by ssp2010 (see edit history)

[8ennett 0]

Yes, the answer to this problem is you need to use a while loop. You can use any other loop as well but i find the while loop the easiest to use for multiple results.

ok so let's assume there are four different entries under Honda, and the fields listed are type, id, colour and reg (I know they're not but for the sake of an example). So here is how we would process the data.

We need to construct the outside of the html table before looping the results inside the table. I'm assuming you know how to use tables with and . Once the shell of the table has been constructed you need to loop the data inside the table using mysql_fetch_assoc inside a loop. Here's a full example of what I mean.

$result = mysql_query("SELECT * FROM cars WHERE model='Honda'");   echo '<table>';   while ($row = mysql_fetch_assoc($result)){   echo '<tr>	<td>'.$row['type'].'</td>	<td>'.$row['id'].'</td>	<td>'.$row['colour'].'</td>	<td>'.$row['reg'].'</td>	</tr>';	 }	 echo '  </table>	';

So what is happening now is, the variable $row will now be used as an array per row per loop in the while function. If there were three result in the db for the query then it will loop three different rows in the table with four columns all the way down.

Like I said, the while loop is probably the easiest to use for multiple rows in a db query but you can use the for and foreach loops as well.

Hope that was what you were looking for!

(Also I'm not sure if it was your terminology or you are actually using different tables for each model, but if instead of using different tables per model use the same table and just add an additional field for the type, trust me in the long run it will make your db much more organised and streamlined)

[yordan 10]

Of course, first of all try this in order to check that it meets your needs.Then, from a SI architecture point of view, we should have a second step of thinking : does this not mean a full table scan ? If this is the case, and if we have a lot of users doing this, this will be costly from disk throughput point of view and then, the query should be optimized.

[8ennett 0]

excellent point yordan, perhaps build in a pagination system using the GET variables, eg. yoursite.com?page=1 and process the page number to use the LIMIT function in your query.

[ssp2010 0]

Hi, guys thanks for the reply. Bennet i've tried what you've told me which makes more sense by adding the makes in one table. The thing is now when I try to populate the table, say i want to add a car under only "honda" and the "description" field. MYSQL gives an error saying "field 'acura' doesnt have a default value." I'm trying to get the select drop downs or the search box to find only the information being selected from the table and not the other makes. ;Here's what I've been working, I don't know if it will help much. ________________________________________________________________________________________________________ (here's the search form) <body> <h1>Cars 4 Sale</h1> <form action="sresults.php" method="post"> Choose search type:<br/> <select name="searchtype"> <option value="acura">acura</option> <option value="ford">ford</option> <option value="honda">Honda</option> <option value="lexus">Lexus</option> </select> <br/> Search Vehicle: <br/> <input name="searchterm" type="text" size="40"/> <br/> <input type="submit" name="submit" value="Search"/> </form> </body> _________________________________________________________________________________________________________________________________________ (here's the php script to search the database..) body> <h1> Local Cars 4 sale</h1> <?php // create short varilable names $searchtype=$_POST['searchtype']; $searchterm=trim($_POST['searchterm']); if(!searchtype || !searchterm){ echo 'You have not entered any search details.'; exit; } if(!get_magic_quotes_gpc()){ $searchtype=addslashes($searchtype); $searchterm=addslashes($searchterm); } @$db=new mysqli('localhost','root','*********', 'cars'); if(mysqli_connect_errno()){ echo 'Error: could not connect to database'; exit; } $query="select * from vehicles where ".$searchtype." like '%".$searchterm."%'"; $result=$db->query($query); $num_results=$result->num_rows; echo "<p>Number of cars found: " .$num_results."</p>"; for($i=0; $i<num_results; $i++){ $row=$result->fetch_assoc(); if($searchtype=="honda"){ echo "<p>".($i+1).($row['honda'])."</p>"; }elseif($searchtype=="ford"){ echo "<p>". ($i+1).($row['ford'])."</p>"; }elseif($searchtype=="lexus"){ echo "<p>". ($i+1).($row['lexus'])."</p>"; }elseif($searchtype=="acura"){ echo "<p>". ($i+1).($row['acura'])."</p>"; }else{ echo"<p> no search was entered</p>"; } } $result->free(); $db->close(); ?> </body>

[8ennett 0]

Ok from the sound of it you have create a table with seperate fields for each car type, and when you try to insert into the table there is no default value set for each make of car. Try creating a single table for all the cars and just use different values, or even use you the enum to create different values. Try creating the table like this: CREATE TABLE 'vehicles' ( `id` int(10) NOT NULL auto_increment, `make` varchar(10) NOT NULL default 'Honda', PRIMARY KEY ('id') ) I know there are more fields but I haven't seen your table structure. INSERT INTO vehicles (make) VALUES ('Honda'); INSERT INTO vehicles (make) VALUES ('Ford'); INSERT INTO vehicles (make) VALUES ('Acura'); Then all you need to do is add each car type in to the 'make' field and simply query the make of the car to return the rows from the table. SELECT * FROM vehicles WHERE make='Honda';

[ssp2010 0]

Thanks bennet for taking the time and helping me out. I've tried what you yold me on the last post, im stuck now where im in the script to display the results . Heres my MSQL I've created a DB called 'cars'.under cars i've created the table vehicles and heres what I created under vehicles......Create table vehicle(id mediumint unsigned not null auto_increment,make varchar(10)not null,description varchar(4000) not null,primary key (id));I've populated the vehicle table with different cars .I'm not sure if you still can see my search form and script page but here it is again just in case you can't view itHeres the search form page<h1>Cars 4 Sale</h1> <form action="sresults.php" method="post"> Choose search type:<br/> <select name="searchtype"> <option value="acura">acura</option> <option value="ford">ford</option> <option value="honda">Honda</option> <option value="lexus">Lexus</option> </select> <br/> Search Vehicle: <br/> <input name="searchterm" type="text" size="40"/> <br/> <input type="submit" name="submit" value="Search"/> </form>__________________________________________________________________Here's the php page I'm having problems with the looping and how I want the results to be displayed. Lets say I want the user to be able to select "honda" also being able to type in what specific model for "honda" they are searching for and have the results displayed if they do not type anything in the box and select still honda they will get everything thats under honda. Heres the php script again I have so far. Can you show the way you would code in the search results and let me know if there is anything missing? thanks<?php// create short varilable names$searchtype=$_POST['searchtype'];$searchterm=trim($_POST['searchterm']);if(!searchtype || !searchterm){ echo 'You have not entered any search details.'; exit;}if(!get_magic_quotes_gpc()){ $searchtype=addslashes($searchtype); $searchterm=addslashes($searchterm);}@$db=new mysqli('localhost','root','********', 'cars');if(mysqli_connect_errno()){ echo 'Error: could not connect to database'; exit;}$query="select make, description from vehicle where make= ??????? (im not sure how to pick honda only or acura etc from the field 'make' in the table 'vehicle' ".$searchtype." like '%".$searchterm."%'";$result=$db->query($query);$num_results=$result->num_rows;echo "<p>Number of cars found: " .$num_results."</p>";for($i=0; $i<num_results; $i++){ $row=$result->fetch_assoc(); echo "<p>Vehicles." (i+1) (also stuck how to display everything here ) }$result->free();$db->close();?>

[8ennett 0]

Ok, first thing you want to do is complete the search query like this:

"SELECT make, description FROM cars WHERE make='".$searchtype."' AND description LIKE '".$searchterm."'";

So here you are selecting both make and description from the cars table where the make is your search type AND description contains the search term

Now to display the result it would probably be easiest to use the WHILE function and display in a table like so:

echo '<table><tr><td><strong>Make</strong></td><td><strong>Description</strong></td></tr>'; 	 while ($row = $result->fetch_assoc()) {	  echo '<tr><td>'.$row['make'].'</td><td>'.$row['description'].'</td></tr>';	   }   echo '</table>

When all you are doing is running through a list of rows from a sql query then while is always going to be the easiest when used with fetch_assoc(). There's no point in making it more complicated otherwise you can end up confusing yourself. So replace the query in $query with the above and replace the for{} function with the while{} function i've given you above, making sure those echoes just before and just after while{} are there as well. That should properly query the database for you with the intended result and display them in a nice little table. Don't forget, once you've done this you may want to think about building a pagination system so it only displays X amount of results on the page and there are 2 buttons at the bottom "Next Page" and "Previous Page", if you need help with that though then just let me know. It just helps the server and the users browser in case 50,000 results are returned and it's working like made to display them all..

One last piece of advice as well. When building forms to submit data, I find it's easiest to submit them to the page they are on, then all you need to do is add an if statement to split up the code.

if (isset($_POST['submit'])){ // Process form data here } else { // Display web form here }

These just makes it easier to manage php documents on your server once they start to build up, tidier this way.

[8ennett 0]

Could you let us know if this worked for you or not, just so we know if the problem has been resolved for anyone else having the same problem and is reading this.