...

View Full Version : dropdown box query sql database HELP!



mike_tw
04-03-2007, 02:14 PM
ello there!

I'm a noob at this you'll no doubt notice

My problem is, i want to create a feature similar to this one: http://www.jewson.co.uk/en/templates...uestid=2019469

As youll see, you can sort the database by manufacturer, color etc in a way that you can select colour from a particular manufcaturer too.

They use java servlets, i want to use php + mysql.

I have a database in place... i have built a search.htm form with four drop down menus.

This is the results.php page which i know is wrong and doesnt work.


MYSQL_CONNECT($hostname, $username, $password) OR DIE("DB connection unavailable");
@mysql_select_db( "$dbName") or die( "Unable to select database");

function secured($val)
{
$val = strip_tags(trim(($val))) ;
$val = escapeshellcmd($val);
return stripslashes($val);
}
if (get_magic_quotes_gpc())
{
$Manufacturer = $_POST['Manufacturer'];
}
else
{
$Manufacturer = addslashes($_POST['Manufacturer']);
}
$Manufacturer = secured($Manufacturer);

if ($Manufacturer!= "SelectManufacturer") //The Default select value
{
$query = "SELECT * FROM bricks WHERE Manufacturer=$Manufacturer1";
$result = mysql_query($query);
$num_rows = mysql_num_rows($result);
if(!$num_rows)
{
echo "<table><tr><td align='center'>No data found 0 record returned</td></tr></table>";
}
else
{
echo "<table align='center'><tr>";
echo "<td align='center'><b>Manufacturer</b></td>";
echo "<td align='center'><b>Colour</b></td>";
echo "<td align='center'><b>Texture</b></td>";
echo "<td align='center'><b>Manufacturing Process</b></td>";
echo "</tr>";

while($row = mysql_fetch_array($result, MYSQL_ASSOC))
echo "<tr><td align='center'>".$row["Manufacturer"]."</td>";
echo "<td align='center'>".$row["Colour"]."</td>";
echo "<td align='center'>".$row["Texture"]."</td>";
echo "<td align='center'>".$row["Manufacturing Process"]."</td>";
echo "</tr>";

}
echo "</table>";
}
?>

brings up :

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/thetdco/public_html/brick/results.php on line 31
No data found 0 record returned

HElp!

Nightfire
04-03-2007, 02:32 PM
You don't have a variable called Manufacturer1



$query = "SELECT * FROM bricks WHERE Manufacturer=$Manufacturer1";

Take the 1 off it

iLLin
04-03-2007, 02:38 PM
You should setup error checking on your query's too. And echo at your query when you get a failed recordset to make sure you are indeed getting the values necessary for you query as Nightfire suggested.

$result = mysql_query($query) or die("failed: mysql_error());

mike_tw
04-03-2007, 02:51 PM
http://www.thetechworld.co.uk/brick/search.htm

Thats my html form

I've tried error reporting in multiple places "OR DIE..."

It just brings up more errosr like unexpected ">" in line 34

Why is it saying it isnt valid resutl resource?

iLLin
04-03-2007, 02:58 PM
$query = "SELECT * FROM bricks WHERE Manufacturer=$Manufacturer1";
//take off the '1' on Manufacturer as suggested my Nightfire
echo $query; //make sure you getting the right values
$result = mysql_query($query) or die ("failed:".mysql_error());
$num_rows = mysql_num_rows($result);


Try that see what you get.

Nightfire
04-03-2007, 02:59 PM
That's as he's forgotten to use closing quotes
Your drop down for manufacturers you've called "Select Manufacturer" you can't have a name with a gap in. Take out Select


$result = mysql_query($query) or die("failed: ".mysql_error());

mike_tw
04-03-2007, 03:25 PM
SELECT * FROM bricks WHERE Manufacturer=failed:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

Thats what happens when i replaced original code with the one suggested

line 1 being :

<?
$hostname = "localhost";
$username = ""; .
$password = "";
$usertable = "bricks";
$dbName = "tt";

removed the details

iLLin
04-03-2007, 03:27 PM
You need to wrap single quotes around your value

$query = "SELECT * FROM bricks WHERE Manufacturer='$Manufacturer'

And by putting the die message there, it told you that. You will learn what different error's tell you :)

Nightfire
04-03-2007, 03:28 PM
You've still got the drop down called "Select Manufacturer". You can't give it a name with a space. As I said before, delete Select. Rename $Manufacturer1 to $Manufacturer in your query

<select size="1" name="Select Manufacturer">

mike_tw
04-03-2007, 03:31 PM
Now the page displays

SELECT * FROM bricks WHERE Manufacturer=''
No data found 0 record returned

Thanks guys for all your help btw. i appreciate it

iLLin
04-03-2007, 03:33 PM
Your not getting your $Manufacturer value obviously, since its blank... Fix that and you should be golden.

Nightfire
04-03-2007, 03:33 PM
You've still got the drop down called "Select Manufacturer". You can't give it a name with a space. As I said before, delete Select.

<select size="1" name="Select Manufacturer">

3rd time lucky.....

mike_tw
04-03-2007, 03:38 PM
I have edited the space out of the SelectManufacture... still no luck.

Im wondering where is the word "Manufactuerer" defined in my search html so that the php can POST it


<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>New Page 1</title>
</head>

<body>

<form method="POST" action="http://www.thetechworld.co.uk/brick/results.php" target="_blank">
<p><select size="1" name="SelectManufacturer">
<option value="Manufacturer1">Manufacturer1</option>
<option value="Manufacturer2">Manufacturer2</option>
<option value="Manufacturer3">Manufacturer3</option>
<option selected value="SelectManufacturer">SelectManufacturer</option>
</select></p>
<p><select size="1" name="Brown">
<option selected value="Select Colour">Select Colour</option>
<option value="Red">Red</option>
<option value="Blue">Blue</option>
<option value="Brown">Brown</option>
</select></p>
<p><select size="1" name="rough">
<option selected>Select Texture</option>
<option value="smooth">smooth</option>
<option value="rough">rough</option>
</select></p>
<p><select size="1" name="Pressed">
<option selected value="SelectManufacturingProcess">SelectManufacturingProcess
</option>
<option value="Wire Cut">Wire Cut</option>
<option value="Hand Made">Hand Made</option>
<option value="Stock">Stock</option>
<option value="Pressed">Pressed</option>
</select></p>
<p><input type="submit" value="Submit" name="B1"></p>
</form>
<p>&nbsp;</p>
</body>

</html>

Nightfire
04-03-2007, 03:40 PM
Delete the word Select (sigh lol)

Or rename the variable


$Manufacturer = $_POST['Manufacturer'];

to


$Manufacturer = $_POST['SelectManufacturer'];

mike_tw
04-03-2007, 03:43 PM
Ok that made it better, now its displaying the table for the records and it is displaying a record. Im not sure why the SELECT * FROM query is still showing thoguh?

SELECT * FROM bricks WHERE Manufacturer='Manufacturer1'
Manufacturer Colour Texture Manufacturing Process
Manufacturer1

mike_tw
04-03-2007, 03:47 PM
i got it,

$query = "SELECT * FROM bricks WHERE Manufacturer=$Manufacturer1";
//take off the '1' on Manufacturer as suggested my Nightfire
echo $query; //make sure you getting the right values
$result = mysql_query($query) or die ("failed:".mysql_error());
$num_rows = mysql_num_rows($result);

it was echoing the query

iLLin
04-03-2007, 03:58 PM
Glad you got it sorted :)

LOL Nightfire, who listens to mods? I mean seriously...

mike_tw
04-03-2007, 04:05 PM
How should i go about creating the rest of the file...

similar to this website : http://www.jewson.co.uk/en/templates...uestid=2019469

The user doesnt haev to select a value in all boxes. They can select only Manufacturer and every record of that manufacturre will display regardless of colour , texture

mike_tw
04-03-2007, 04:09 PM
http://www.thetechworld.co.uk/brick/search.htm

If you select Manufacturer 1, and submit.. you will see it returns the records... but only the manufacturer name. How do i get it to return the colour , texture etc of that record?

Thanks

iLLin
04-03-2007, 04:57 PM
Verify you have values in your database, then post up your code that you are using now.

mike_tw
04-03-2007, 05:31 PM
MYSQL_CONNECT($hostname, $username, $password) OR DIE("DB connection unavailable");
@mysql_select_db( "$dbName") or die( "Unable to select database");

function secured($val)
{
$val = strip_tags(trim(($val))) ;
$val = escapeshellcmd($val);
return stripslashes($val);
}
if (get_magic_quotes_gpc())
{
$Manufacturer = $_POST['Manufacturer'];
}
else
{
$Manufacturer = addslashes($_POST['Manufacturer']);
}
$Manufacturer = secured($Manufacturer);

if ($Manufacturer != "SelectManufacturer") //The Default select value
{
$query = "SELECT * FROM bricks WHERE Manufacturer='$Manufacturer'";
//take off the '1' on Manufacturer as suggested my Nightfire
//make sure you getting the right values
$result = mysql_query($query) or die("failed: ".mysql_error());
$num_rows = mysql_num_rows($result);
if(!$num_rows)
{
echo "<table><tr><td align='center'>No data found 0 record returned</td></tr></table>";
}
else
{
echo "<table align='center'><tr>";
echo "<td align='center'><b>Manufacturer</b></td>";
echo "<td align='center'><b>Colour</b></td>";
echo "<td align='center'><b>Texture</b></td>";
echo "<td align='center'><b>Manufacturing Process</b></td>";
echo "</tr>";

while($row = mysql_fetch_array($result, MYSQL_ASSOC))
echo "<tr><td align='center'>".$row["Manufacturer"]."</td>";
echo "<td align='center'>".$row["Colour"]."</td>";
echo "<td align='center'>".$row["Texture"]."</td>";
echo "<td align='center'>".$row["Manufacturing Process"]."</td>";
echo "</tr>";

}
echo "</table>";
}
?>

im not sure how to take it forward to indlue the other drop downs to do the same. It also doesnt return the colour texture of process of the returned records ; only the manufacturer name

iLLin
04-03-2007, 05:36 PM
echo "<td align='center'>".$row["Manufacturing Process"]."</td>";


You have a space in your field name? I suggest verifying your fields are accurate and correct. I put my money you have them wrong and that's why they are not showing up.

Then to include your other items into your 'search/lookup'. You will need to build out your where query.



<?php

$where = " WHERE ";
if(isset(manufacture)) {
$where .= " manufacturer = $manu";
}
if(isset(color)) {
$where .= " AND color = $color ";
}

//you get the idea

//Then your query would be something like this
$query = "select * from table ".$where;

?>



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum