1. Home
  2. Forums Index
  3. Server Side / PHP Server Side Scripting 3:27 pm Apr 28, 2026

Forum Moderators: coopster

Message Too Old, No Replies

Search DB using data from rows in place of text input

         

macspeed

12:38 pm on Apr 9, 2010 (gmt 0)

10+ Year Member



Hello,
I'm new to PHP and managed to get a simple search script working but can't find how to replace the user text input with a drop down selection populated from my database rows. 
<html> 
<head> 
<basefont face="Arial"> 
</head> 
<body>

<?php

error_reporting(E_ALL); 
if (!isset($_POST['Submit'])) { 
// form not submitted 
?>

<form action="<?=$_SERVER['PHP_SELF']?>" method="post"> 
Search <input type="text" name="search"><br> 
<select size="1" name="dropdown"> 
<option value="" selected>Search By...</option> 
<option value="Manufacturer">Manufacturer</option> 
<option value="Model">Model</option> 
<option value="Socket">Socket</option> 
<option value="Hyperthread">Hyperthread</option> 
<option value="Cores">Cores</option> 
</select> 
<input type="Submit" value="Submit" name="Submit"> 
</form>

<?php 
}

else {

// form submitted 
// set server access variables 
$host = "#*$!X"; 
$user = "#*$!X"; 
$pass = "#*$!X"; 
$db = "#*$!X";

$search = empty($_POST['search'])? die ("ERROR: Enter Search Criteria") : mysql_escape_string($_POST['search']); 
$dropdown = empty($_POST['dropdown'])? die ("ERROR: Select from Dropdown") : mysql_escape_string($_POST['dropdown']);

// Open Connection

$connect = mysql_connect($host, $user, $pass) or die ("Unable to connect to host");

//Select Database

mysql_select_db($db) or die ("Unable to connect to database");

//Create Query

$query = "SELECT * FROM CPU WHERE $dropdown='$search'" or die (mysql_error());

$result = mysql_query($query) or die (mysql_error());

$num=mysql_numrows($result);

mysql_close($connect);

echo "<b><center>Database Output</center></b><br><br>";

$i=0; 
while ($i < $num) {

$Manufacturer=mysql_result($result,$i,"Manufacturer"); 
$Model=mysql_result($result,$i,"Model"); 
$Socket=mysql_result($result,$i,"Socket"); 
$Hyperthread=mysql_result($result,$i,"Hyperthread"); 
$Cores=mysql_result($result,$i,"Cores"); 

echo "<br><b>$Manufacturer</b></br>$Model</b></br>Socket: $Socket</br>Hyperthread: $Hyperthread</br>Cores: $Cores<br>";

$i++;

} 
} 
?>

</body> 
</html>


I want my users to be able to select variables of the CPU configuration which are currently stored in my database under rows with the same name as those currently in the search dropdown (Manufacturer, Model, Cores, etc).

example: User wants to retrieve all CPUs with "4" cores that fit a "1156 socket".

I'd like for the return to be the entire record for the matching CPU.

If anyone has a working example of this I'd greatly appreciate it.

Matthew1980

6:02 pm on Apr 9, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Hi there macspeed,

Welcome to the forum :)

Your query:-

$query = "SELECT * FROM CPU WHERE $dropdown='$search'" or die (mysql_error());

Almost ;-p

Try:-

$query = "SELECT * FROM `CPU` WHERE `".$dropdown."` = '".$search."' ";

That's if I have understood you correctly.

Also, there is no need to attach or die(mysql_error()) to everything, you only need to use it on the query, plus you only really need to use it when you are in development, remove it for production, for security.

This:-

echo "<br><b>$Manufacturer</b></br>$Model</b></br>Socket: $Socket</br>Hyperthread: $Hyperthread</br>Cores: $Cores<br>";

Change to this:-

echo "<br><b>".$Manufacturer."</b></br>".$Model."</b></br>Socket: ".$Socket."</br>Hyperthread: ".$Hyperthread."</br>Cores: ".$Cores."<br>";

This makes it join in the vars when echoing a string.

And for the connection:-

$result = mysql_query($query, $connection)

You need a reference handle in there if it is the first instance of a query, subsequent ones don't need it there ;-p

Careful with this too:-

<form action="<?php $_SERVER['PHP_SELF'];?>" method="post">

Leave the action as action="" when you go public, as there are issues with $_SERVER['PHP_SELF'], google it to see what I mean, I only found this out a few weeks back. Leaving the action part blank, when posting or actioning the form defaults to itself, so effectively the $_SERVER['PHP_SELF']; is redundant anyway. If you do use it for dev work, make sure that the ; is on the end too! ie: $_SERVER['PHP_SELF'];, and only use short tags if your php.ini file has it enabled, full tags are preferred, and most servers don't support the use of short tags anyway.

Also I think as the $i = "0"; & $i++ are not needed, as you are already in a loop, unless there is another reason it is there?

Hope this helps a little,

Cheers,
MRb

macspeed

7:29 am on Apr 10, 2010 (gmt 0)

10+ Year Member



The search is working better than before but it's still based on the user typing in a text value for the query. Since I didn't build it to use the "like" operation the user has to type in exactly what is in the database or they don't get any results back from the search.

What I want is to replace the user text input with a drop down selection that is filled in from the database.

For example, if the user wants to search based on number of cores they would select "cores" and the second drop down would be populated with each unique entry in the "cores" field of my database.

Right now I have around 25 distinct CPU records in the database and the "cores" fields are either "1", "2" or "4".

First dropdown select "cores", second dropdown select "1, 2 or 4" (pulled from the "cores" field in the table) and the return should be every CPU that has a matching entry in the "cores" field.

It wouldn't be a big deal to hard code the search variables into a dropdown for this example but I have other fields with quite a few different values. I'd like the search tool to be dynamic so as I add more CPU records the search field variables are automatically added.

Readie

11:06 am on Apr 10, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



I think this is what you're after: 

$sql = 'SELECT DISTINCT cpu FROM cpu_table ORDER BY cpu ASC';
$result = mysql_query($sql);
$rows = mysql_num_rows($result);

$sel = '<select name="cpu">
<option value=""></option>';

for($i = 0; $i < $rows; $i++) {
$cpu = mysql_result($result, $i, "cpu");
$sel .= "\n" . '<option value="' . $cpu . '">' . ucfirst($cpu) . '</option>';
}
$sel .= "\n" . '</select>';
echo $sel;

macspeed

2:59 pm on Apr 10, 2010 (gmt 0)

10+ Year Member



Thanks, I'm not sure how to integrate that into my code though. I'm really, really new to PHP. (My coding background was in designing applications for teams of coders to build and hands on building home automation programs using Phast / Panka / AMX / Crestron, all based on simple IDE's but it was good for learning conditional logic trees).

Obviously I'd have to replicate this for each field I want them to be able to search on.

I realize I should be learning the theory behind the coding but I learn best by seeing code and editing it, then filling in the theory once I know how it all fits in practice.

I'd be willing to pay for someone's time to put it all together for one of the search terms, I could fill in the others once I know how it should all connect.

Readie

3:06 pm on Apr 10, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



That's a stand alone piece of code for creating the select menu.

You could quite easily create a function out of it: 

function select_menu($cell, $table) {
$sql = 'SELECT DISTINCT `' . $cell . '` FROM `' . $table . '` ORDER BY `' . $cell . '` ASC';
$result = mysql_query($sql);
if($rows = mysql_num_rows($result)) {

$sel = '<select name="' . $cell . '">
<option value=""></option>';

for($i = 0; $i < $rows; $i++) {
$cpu = mysql_result($result, $i, $cell);
$sel .= "\n" . '<option value="' . $cpu . '">' . ucfirst($cpu) . '</option>';
}
$sel .= "\n" . '</select>';
} else {
$sel = '&nbsp;';
}
return $sel;
}
echo select_menu('cpu', 'cpu_table');

macspeed

4:23 pm on Apr 11, 2010 (gmt 0)

10+ Year Member



I'd be willing to Paypal you 40 dollars to place it into the code I posted and explain how I would go about adding the other search variables into it.

Readie

6:10 pm on Apr 11, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



I'm not going to take your money, though it's nice to have an offering :P

Paste that function I wrote right underneath the

<?php

So it ends up as 

<?php

function select_menu($cell, $table) {

Then, anywhere in the same document, you can write this: 

echo select_menu('COLUMN HEADER', 'TABLE NAME');

replacing COLUMN HEADER and TABLE NAME with the name of the column with the data you want the select menu to be populated with, and the TABLE NAME replaced with the appropriate MySQL table name - and it will create the select menu for you.

macspeed

7:54 am on Apr 12, 2010 (gmt 0)

10+ Year Member



Like this? 

<html> 
<head> 
<basefont face="Arial"> 
</head> 
<body>

<?php
function select_menu($cell, $table) { 
$sql = 'SELECT DISTINCT `' . $cell . '` FROM `' . $table . '` ORDER BY `' . $cell . '` ASC'; 
$result = mysql_query($sql); 
if($rows = mysql_num_rows($result)) { 

$sel = '<select name="' . $cell . '"> 
<option value=""></option>'; 

for($i = 0; $i < $rows; $i++) { 
$cpu = mysql_result($result, $i, $cell); 
$sel .= "\n" . '<option value="' . $cpu . '">' . ucfirst($cpu) . '</option>'; 
} 
$sel .= "\n" . '</select>'; 
} else { 
$sel = '&nbsp;'; 
} 
return $sel; 
}

// form submitted 
// set server access variables 
$host = "----"; 
$user = "===="; 
$pass = "----"; 
$db = "-----";

// Open Connection

$connect = mysql_connect($host, $user, $pass) or die ("Unable to connect to host");

//Select Database

mysql_select_db($db) or die ("Unable to connect to database");

//Create Query

echo select_menu('CPU', 'CPU');

$i++;
?>
</body> 
</html>

Readie

2:14 pm on Apr 12, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



That will create a select menu, yes, but you'll still need to create the rest of the form. 

mysql_select_db($db) or die ("Unable to connect to database");

//Create Query
?>
<form method="post" action="/somefile.php">
<?php
echo select_menu('CPU', 'CPU');
?>
<input type="submit" name="subbut" value="Submit" />
</form>
<?php
$i++;
?>

Though I'm not sure what that $i++; is supposed to be doing :/

Matthew1980

2:22 pm on Apr 12, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Hi there Readie,

Though I'm not sure what that $i++; is supposed to be doing :/


I was wondering this on my original post, there is already a loop there. The only thing it does is give a count each time there is an instance being echoed, so it may be a debugging thingy...

Good sollution for building the select menu though :)

macspeed: Have fun with the rest of yor site, you have a nice snippet there from Readie...


Cheers,
MRb

macspeed

7:13 pm on Apr 12, 2010 (gmt 0)

10+ Year Member



I'm missing something, I get the following error now:
"Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/test/public_html/search.php on line 11"

The $i++; was in the original script I used as a template, there was an issue with that script "recycling" back to a ready state after a search which I think the original dev was trying to fix. I forgot all about it actually.

macspeed

7:30 pm on Apr 12, 2010 (gmt 0)

10+ Year Member



I found part of the issue - I didn't define the column header correctly.

Changed it to "Cores" and I have a drop down box populated by cores value (1, 2 and 4) with a "search" button. There's no indicator what the search is for so I'm guessing I'll have to add that via HTML as a table header above the search area.

Tried to execute a search and no result, the page refreshes back to pre- select condition.

Here's the current search.php: 

<html> 
<head> 
<basefont face="Arial"> 
</head> 
<body>

<?php
function select_menu($cell, $table) { 
$sql = 'SELECT DISTINCT `' . $cell . '` FROM `' . $table . '` ORDER BY `' . $cell . '` ASC'; 
$result = mysql_query($sql); 
if($rows = mysql_num_rows($result)) { 

$sel = '<select name="' . $cell . '"> 
<option value=""></option>'; 

for($i = 0; $i < $rows; $i++) { 
$cpu = mysql_result($result, $i, $cell); 
$sel .= "\n" . '<option value="' . $cpu . '">' . ucfirst($cpu) . '</option>'; 
} 
$sel .= "\n" . '</select>'; 
} else { 
$sel = '&nbsp;'; 
} 
return $sel; 
}
 
// set server access variables 
$host = "-"; 
$user = "-"; 
$pass = "-"; 
$db = "-";

// Open Connection

$connect = mysql_connect($host, $user, $pass) or die ("Unable to connect to host");

//Select Database

mysql_select_db($db) or die ("Unable to connect to database"); 

// form submitted 
?> 
<form method="post" action="/search.php"> 
<?php 
echo select_menu('Cores', 'CPU'); 
?> 
<input type="Submit" name="Submit" value="Submit" /> 
</form> 
<?php
?>
</body> 
</html>

Readie

10:56 pm on Apr 12, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Tried to execute a search and no result, the page refreshes back to pre- select condition.

Well that's because you're not doing anything with the data. 

echo select_menu('Cores', 'CPU');

You've specified Cores as the column header now, so you can aquire the data like this: 

$_POST['Cores']

And that will contain the value that was selected when the user pressed submit.

By the way, note that you can use that function for several columns at the same time. 

echo select_menu('Cores', 'CPU') . select_menu('Processors', 'CPU');

Will output two select menus (with nothing between them, may want to look into some table formatting :P)

macspeed

1:01 am on Apr 13, 2010 (gmt 0)

10+ Year Member



I'd have to see it in context (very new to this, the offer to pay still stands, btw).

Readie

1:58 am on Apr 13, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Well, after the form has been posted, there is an array ($_POST) full of all the form elements. 

<input type="text" name="hello">

The above input, after form submission. Whatever had been entered into this text field can be used like any other variable. See: 

echo $_POST['hello'];

Because you're using this in a SQL select statement, and as a filter, you would do something like this on the target page: 

$sql = 'SELECT `Cores`, `Processors` FROM `CPU` WHERE 1';

if(isset($_POST['Cores']) && $_POST['Cores'] != "") {
$sql .= ' AND `Cores` = "' . mysql_real_escape_string(strip_tags($_POST['Cores'])) . '"';
}

$sql .= ' ORDER BY `Processors` ASC';

$result = mysql_query($sql);
$rows = mysql_num_rows($result);

echo '<table cellspacing="2">
<tr>
<td>#</td>
<td><b>Processor</b></td>
<td><b>Cores</b></td>
</tr>';
for($i = 0; $i < $rows; $i++) {
$rc = ($i%2)? 'ffff' : 'cccc';
echo "\n" . '<tr style="background-color: #' . $rc . 'ff;">
<td>' . ($i + 1) . '</td>
<td>' . mysql_result($result, $i, "Processors") . '</td>
<td>' . mysql_result($result, $i, "Cores") . '</td>
</tr>';
}
echo "\n" . '</table>';

(Note: I don't know the name of the column containing the CPU names, so be sure to change all references to "Processors" to the correct column header)

Anyways, the above would apply a filter based on the number of cores selected. If a blank value was selected (or no value at all) then all rows from the table "CPU" would be shown. If a value was selected, then the result would be limited to however many cores were selected. 

$sql = 'SELECT `Cores`, `Processors` FROM `CPU` WHERE 1';
We're building the base SQL query here. Note that "WHERE 1" is kind of like saying "Where there is anything to select" - In lay man's terms, it doesn't do anything aside from get the WHERE clause up there (so we can add additional WHERE clauses with ease). 

if(isset($_POST['Cores']) && $_POST['Cores'] != "") {
$sql .= ' AND `Cores` = "' . mysql_real_escape_string(strip_tags($_POST['Cores'])) . '"';
}
If a value has been provided for $_POST['Cores'] this will add an additional WHERE clause to the existing SQL query. Note that there is a space before the "AND". 

mysql_real_escape_string(strip_tags($_POST['Cores']))
The functions being applied here are to protect you from users with malicious intent.
mysql_real_escape_string() prevents users from entering their own SQL
strip_tags() prevents users from entering their own PHP
You may think "Hey, it's a select menu, they can't enter their own values" - think again. An off-site form that posts to the same location as yours, can have different input types (i.e. text/textarea) with the same names. Don't ever forget to include those functions. 

$result = mysql_query($sql);
$rows = mysql_num_rows($result);
Here we run the query, and find out how many rows of data are returned. 

for($i = 0; $i < $rows; $i++) {
This line creates a variable $i, and sets it as 0. Everything contained within the curly braces will loop over and over, and each time $i will increment by 1. This continues until $i is equal to $rows (will not run when $i is exactly equal to $rows) 

mysql_result($result, $i, "Cores")
This pulls the information contained within a specific cell. In this case, whatever row number $i is equal to, under the column "Cores". 

$rc = ($i %2)? 'ffff' : 'cccc';
This line is just there to alternate the table row colour. If $i is a multiple of 2 will determine if $rc is equal to 'ffff' or 'cccc'.
Note that this is the same as saying 
if($i %2) {
$rc = 'ffff';
} else {
$rc = 'cccc';
}

macspeed

4:11 am on Apr 13, 2010 (gmt 0)

10+ Year Member



Why aren't there any complete examples of this anywhere?

OK - so when a user sends a search query via the dropdown menu(s) the results are sent into an array which has to be retrieved and formatted.

Does that mean this second chunk of code needs to go on a separate php page from the search input page and designated in the "form method="post" action="/results.php"> "?

The table name is "CPU" - here are the column headers:

Manufacturer - Model - Frequency - 64 Bit - CPU - Cache Memory - Core Name - Process - Stepping - Wattage - BCLK - Hyperthread - Cores -Type - Socket - Features - Average Price (USD) - Intel Part Number - UPC - URL

I don't want all of these to be search variables, of course. Manufacturer, Cores, Socket and maybe price. (That's actually going to be the really complex bit is coding up an automated price lookup that will grab the "avg best" price off Google shopping and write it into the database but that's a completely separate deal).

That's why I'm offering to pay for the help - I'm not knowledgeable enough to take a snippet and write an entire function around it yet. I understand what each function of the code does and can edit a working example but I need to see it in the context of a complete program / function.

I know it seems like I'm going about it the long way but this is how I taught myself html, xml, css and bash shell. If I can get this thing complete and working with one search variable I can modify it and add the others in and will hopefully come out of it knowing how to do it for myself the next time.

macspeed

4:35 am on Apr 13, 2010 (gmt 0)

10+ Year Member



OK I got it...

Created another page - return.php and added it to the definition on search.php. 

<html> 
<head> 
<basefont face="Arial"> 
</head> 
<body>

<?php
// set server access variables 
$host = "#*$!x"; 
$user = "#*$!x"; 
$pass = "#*$!x"; 
$db = "#*$!x";

// Open Connection

$connect = mysql_connect($host, $user, $pass) or die ("Unable to connect to host");

//Select Database

mysql_select_db($db) or die ("Unable to connect to database"); 

$sql = 'SELECT `Cores`, `Model` FROM `CPU` WHERE 1'; 

if(isset($_POST['Cores']) && $_POST['Cores'] != "") { 
$sql .= ' AND `Cores` = "' . mysql_real_escape_string(strip_tags($_POST['Cores'])) . '"'; 
} 

$sql .= ' ORDER BY `Model` ASC'; 

$result = mysql_query($sql); 
$rows = mysql_num_rows($result); 

echo '<table cellspacing="2"> 
<tr> 
<td>#</td> 
<td><b>Processor</b></td> 
<td><b>Cores</b></td> 
</tr>'; 
for($i = 0; $i < $rows; $i++) { 
$rc = ($i%2)? 'ffff' : 'cccc'; 
echo "\n" . '<tr style="background-color: #' . $rc . 'ff;"> 
<td>' . ($i + 1) . '</td> 
<td>' . mysql_result($result, $i, "Model") . '</td> 
<td>' . mysql_result($result, $i, "Cores") . '</td> 
</tr>'; 
} 
echo "\n" . '</table>'; 
?>
</form> 
</body> 
</html>


Now I just have to add in a search reset to return the user back to the search form and add formatting to the search.php page.

Performed search and the return.php page comes up with this result (search for Cores ="2")

# Processor Cores
1 Core™ i3-530 2
2 Core™ i3-540 2
3 Core™ i5-650 2
4 Core™ i5-660 2
5 Core™ i5-661 2
6 Core™ i5-670 2
7 Pentium G6950 2

Readie

11:21 am on Apr 13, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



You don't need a target page, you could have them both on the same .php document, and simply not set an action attribute in the form tag.

Anyways, I'm glad you got your head around it in the end. Just make another post here if you have any future problems :)

And by the way: I was just the same when I was learning PHP :P

macspeed

1:31 pm on Apr 13, 2010 (gmt 0)

10+ Year Member



Thank you for your patience and help, I seriously couldn't have done it without you folks.

Now I just need to work on adding in the other search variables and then formatting the page.

I did try to do it all on one page at first but I must have left in the action attribute, it just wasn't working. I don't mind the two page solution though, I can do more with the html format that way.

I'll keep posting the code as it goes if you guys don't mind, that way the next person searching for this has the example to work from.

macspeed

2:23 pm on Apr 16, 2010 (gmt 0)

10+ Year Member



Typical newb mistake - I completely forgot something.

I plan to use multiple drop downs for the search but the conditional nature of the query completely slipped my mind.

I need for the users to be able to select multiple drop down elements to return the filtered results. For example - they select "2" for cores and "775" for socket and the return is every matching DB entry with 2 for cores and 775 for socket.

Will the existing query do that if I add in the other drop downs or would a different type of query be required for that function?

I have the multiple drop downs working, replaced this part of search.php: 

// form submitted 
?> 
<form method="post" action="return.php"> 
<?php 
echo select_menu('Manufacturer', 'CPU') . select_menu('Cores', 'CPU') . select_menu('Socket', 'CPU') . select_menu('Average Price (USD)', 'CPU'); 
?> 
<input type="Submit" value="Submit" name="Submit"> 
</form> 
</body> 
</html> 

I'm not sure how to get the multiple fields to work in a query for return.php however.

Readie

3:02 pm on Apr 16, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member 



if(isset($_POST['Cores']) && $_POST['Cores'] != "") {
$sql .= ' AND `Cores` = "' . mysql_real_escape_string(strip_tags($_POST['Cores'])) . '"';
}

Just duplicate, and modify the 
$_POST['[b]Whatever[/b]']
accordingly.

macspeed

11:44 am on Apr 17, 2010 (gmt 0)

10+ Year Member



It's throwing an error now so I must not be getting it: 

//Select Database

mysql_select_db($db) or die ("Unable to connect to database"); 

$sql = 'SELECT `Cores`, `Model` FROM `CPU` WHERE 1';

if(isset($_POST['Manufacturer']) && $_POST['Manufacturer'] != "") { 
$sql .= ' AND `Manufacturer` = "' . mysql_real_escape_string(strip_tags($_POST['Manufacturer'])) . '"'; 
} 

if(isset($_POST['Cores']) && $_POST['Cores'] != "") { 
$sql .= ' AND `Cores` = "' . mysql_real_escape_string(strip_tags($_POST['Cores'])) . '"'; 
}

$sql .= ' ORDER BY `Model` ASC'; 

$result = mysql_query($sql); 
$rows = mysql_num_rows($result); 

echo '<table cellspacing="2"> 
<tr> 
<td>#</td> 
<td><b>Manufacturer</b></td>
<td><b>Processor</b></td> 
<td><b>Cores</b></td> 
</tr>'; 
for($i = 0; $i < $rows; $i++) { 
$rc = ($i%2)? 'ffff' : 'cccc'; 
echo "\n" . '<tr style="background-color: #' . $rc . 'ff;"> 
<td>' . ($i + 1) . '</td> 
<td>' . mysql_result($result, $i, "Manufacturer") . '</td>
<td>' . mysql_result($result, $i, "Model") . '</td> 
<td>' . mysql_result($result, $i, "Cores") . '</td> 
</tr>'; 
} 
echo "\n" . '</table>'; 
?>
</form> 
</body> 
</html>


Do I have to modify the Select statement? 

$sql = 'SELECT `Cores`, `Model` FROM `CPU` WHERE 1';


I tried adding 'Manufacturer' into it but that also threw an error

Readie

12:04 pm on Apr 17, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member 



$sql = 'SELECT `Cores`, `Model`, `Manufacturer` FROM `CPU` WHERE 1';

Should make it work, there's no reason that would error unless you either:

A. Miss-spelt the column headers
B. Used a quotation mark ( " OR ' ) instead of a backtick ( ` ) around the column header name.

You don't need to modify that part of the SQL statement if you just want to add to the filter though. You can say 

SELECT `a` FROM `b` WHERE `c` = "d"

macspeed

12:42 pm on Apr 17, 2010 (gmt 0)

10+ Year Member



Does the order of the variables make any difference? I swear I tried the exact same thing but in a different order and it threw an error "unexpected T statement" but using yours it works fine now.

I'm going to try adding in the other search options and see if it all comes together now. I owe you big time on this one, thank you.

Readie

12:49 pm on Apr 17, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



"unexpected T statement"

That would of arisen because you tried to use single quotes instead of back ticks :)

(Assuming you are using a QWERTY keyboard) A backtick is the key just to the left of the (1 !) key, and just above the tab key, without a shift/alt/ctrl modifier being applied.

macspeed

1:18 pm on Apr 17, 2010 (gmt 0)

10+ Year Member



I'm almost there - for some reason all the search variables work except the last two - '64 Bit CPU' and 'Average Price (USD)'. No matter what I select on those two it returns the entire record set.

search page: 

// form submitted 
?> 
<form method="post" action="return.php"> 
<?php 
echo select_menu('Manufacturer', 'CPU') . select_menu('Cores', 'CPU') . select_menu('Socket', 'CPU') . select_menu('64 Bit CPU', 'CPU') . select_menu('Average Price (USD)', 'CPU'); 
?> 
<input type="Submit" value="Submit" name="Submit"> 
</form> 
</body>


return.php data section: 


$sql = 'SELECT `Cores`, `Model`, `64 Bit CPU`, `Manufacturer`, `Average Price (USD)` FROM `CPU` WHERE 1';

if(isset($_POST['Manufacturer']) && $_POST['Manufacturer'] != "") { 
$sql .= ' AND `Manufacturer` = "' . mysql_real_escape_string(strip_tags($_POST['Manufacturer'])) . '"'; 
} 

if(isset($_POST['Cores']) && $_POST['Cores'] != "") { 
$sql .= ' AND `Cores` = "' . mysql_real_escape_string(strip_tags($_POST['Cores'])) . '"'; 
}

if(isset($_POST['64 Bit CPU']) && $_POST['64 Bit CPU'] != "") { 
$sql .= ' AND `64 Bit CPU` = "' . mysql_real_escape_string(strip_tags($_POST['64 Bit CPU'])) . '"'; 
}

if(isset($_POST['Average Price (USD)']) && $_POST['Average Price (USD)'] != "") { 
$sql .= ' AND `Average Price (USD)` = "' . mysql_real_escape_string(strip_tags($_POST['Average Price (USD)'])) . '"'; 
}

$sql .= ' ORDER BY `Model` ASC';


Is it the spaces in the variables?

macspeed

1:32 pm on Apr 17, 2010 (gmt 0)

10+ Year Member



Yep, it was the spaces.
I edited the column names in my database and fixed them in the PHP. It's working 100 percent now.

macspeed

1:36 pm on Apr 17, 2010 (gmt 0)

10+ Year Member



OK for the next poor newb searching for how to do this, the completed code minus formatting on search.php. (I have to work out how to format the search drop down with text headers over them, flat html over the php is proving tricky)

search.php 

<html> 
<head> 
<basefont face="Arial"> 
</head> 
<body>
<?php
function select_menu($cell, $table) { 
$sql = 'SELECT DISTINCT `' . $cell . '` FROM `' . $table . '` ORDER BY `' . $cell . '` ASC'; 
$result = mysql_query($sql); 
if($rows = mysql_num_rows($result)) { 

$sel = '<select name="' . $cell . '"> 
<option value=""></option>'; 

for($i = 0; $i < $rows; $i++) { 
$cpu = mysql_result($result, $i, $cell); 
$sel .= "\n" . '<option value="' . $cpu . '">' . ucfirst($cpu) . '</option>'; 
} 
$sel .= "\n" . '</select>'; 
} else { 
$sel = '&nbsp;'; 
} 
return $sel; 
}
 
// set server access variables 
$host = "zz"; 
$user = "zz"; 
$pass = "zz"; 
$db = "zz";

// Open Connection

$connect = mysql_connect($host, $user, $pass) or die ("Unable to connect to host");

//Select Database

mysql_select_db($db) or die ("Unable to connect to database"); 

// form submitted 
?> 
<form method="post" action="return.php"> 
<?php 
echo select_menu('Manufacturer', 'CPU') . select_menu('Cores', 'CPU') . select_menu('Socket', 'CPU') . select_menu('x64', 'CPU') . select_menu('Price', 'CPU'); 
?> 
<input type="Submit" value="Submit" name="Submit"> 
</form> 
</body> 
</html>


And the search results page, return.php: 

<html> 
<head> 
<basefont face="Arial"> 
</head> 
<body>

<?php
// set server access variables 
$host = "zz"; 
$user = "zz"; 
$pass = "zz"; 
$db = "zz";

// Open Connection

$connect = mysql_connect($host, $user, $pass) or die ("Unable to connect to host");

//Select Database

mysql_select_db($db) or die ("Unable to connect to database"); 

$sql = 'SELECT `Cores`, `Model`, `x64`, `Manufacturer`, `Price` FROM `CPU` WHERE 1';

if(isset($_POST['Manufacturer']) && $_POST['Manufacturer'] != "") { 
$sql .= ' AND `Manufacturer` = "' . mysql_real_escape_string(strip_tags($_POST['Manufacturer'])) . '"'; 
} 

if(isset($_POST['Cores']) && $_POST['Cores'] != "") { 
$sql .= ' AND `Cores` = "' . mysql_real_escape_string(strip_tags($_POST['Cores'])) . '"'; 
}

if(isset($_POST['x64']) && $_POST['x64'] != "") { 
$sql .= ' AND `x64` = "' . mysql_real_escape_string(strip_tags($_POST['x64'])) . '"'; 
}

if(isset($_POST['Price']) && $_POST['Price'] != "") { 
$sql .= ' AND `Price` = "' . mysql_real_escape_string(strip_tags($_POST['Price'])) . '"'; 
}

$sql .= ' ORDER BY `Model` ASC'; 

$result = mysql_query($sql); 
$rows = mysql_num_rows($result); 

echo '<table cellspacing="2"> 
<tr> 
<td>#</td> 
<td><b>Manufacturer</b></td>
<td><b>Processor</b></td> 
<td><b>Cores</b></td>
<td><b>64 Bit CPU</b></td>
<td><b>Average Price (USD)</b></td>
</tr>'; 
for($i = 0; $i < $rows; $i++) { 
$rc = ($i%2)? 'ffff' : 'cccc'; 
echo "\n" . '<tr style="background-color: #' . $rc . 'ff;"> 
<td>' . ($i + 1) . '</td> 
<td>' . mysql_result($result, $i, "Manufacturer") . '</td>
<td>' . mysql_result($result, $i, "Model") . '</td> 
<td>' . mysql_result($result, $i, "Cores") . '</td>
<td>' . mysql_result($result, $i, "x64") . '</td>
<td>' . mysql_result($result, $i, "Price") . '</td>
</tr>'; 
} 
echo "\n" . '</table>'; 
?>
</form> 
</body> 
</html>


Again, thank you very, very, very much for the help.

Readie

2:03 pm on Apr 17, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



You're welcome :)

I suggest using underscores instead of spaces in future - for all names to be used in coding.

Spaces are a head ache.
This 38 message thread spans 2 pages: 38
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

  1. Home
  2. Forums Index
  3. Server Side / PHP Server Side Scripting 3:27 pm Apr 28, 2026