...

View Full Version : Query not retrieving more than one value from a field



brow51
05-10-2008, 12:00 PM
hi,
For some reason my query would return the values of a field:
I am using check boxes and putting statements to see if they are checked... if so then I set a variable. I then use that variable in the query, but for some reason it is just allowing me to select one field value instead of multiple ones. Can anyone help me.


the results page code:

<a href="diamond_search.php">search</a> | <a href="results.php">view list</a> | <a href="add_diamond.php">add diamond</a>
<?php
//checkboxes for shape field
$searcharea = addslashes($_POST['searcharea']);
$searchshape = addslashes($_POST['searchshape']);
$searchshapetwo = addslashes($_POST['searchshapetwo']);
$searchshapethree = addslashes($_POST['searchshapethree']);
$searchshapefour = addslashes($_POST['searchshapefour']);
$searchshapefive = addslashes($_POST['searchshapefive']);
$searchshapesix = addslashes($_POST['searchshapesix']);
$searchshapeseven = addslashes($_POST['searchshapeseven']);
$searchshapeeight = addslashes($_POST['searchshapeeight']);
$searchshapenine = addslashes($_POST['searchshapenine']);
$searchshapeten = addslashes($_POST['searchshapeten']);
$searchshapeeleven = addslashes($_POST['searchshapeeleven']);

///checkboxes for lab field
$searchlab = addslashes($_POST['searchlab']);
$searchlabtwo = addslashes($_POST['searchlabtwo']);
$searchlabthree = addslashes($_POST['searchlabthree']);
$searchlabfour = addslashes($_POST['searchlabfour']);
$searchlabfive = addslashes($_POST['searchlabfive']);
$searchlabsix = addslashes($_POST['searchlabsix']);

//droplists for color field
$colorfrom = addslashes($_POST['colorfrom']);
$colorto = addslashes($_POST['colorto']);


//droplists for clarity field
$searchclarityfrom = addslashes($_POST['searchclarityfrom']);
$searchclarityto = addslashes($_POST['searchclarityto']);

//prices
$searchpricefrom = addslashes($_POST['searchpricefrom']);
$searchpriceto = addslashes($_POST['searchpriceto']);

//droplist for carat field
$searchcaratsfrom = addslashes($_POST['searchcaratsfrom']);
$searchcaratsto = addslashes($_POST['searchcaratsto']);

$searcharea = trim ($searcharea);
$searchshape = trim ($searchshape);
$searchshapetwo = trim ($searchshapetwo);
$searchshapethree = trim ($searchshapethree);
$searchshapefour = trim ($searchshapefour);
$searchshapefive = trim ($searchshapefive);
$searchshapesix = trim ($searchshapesix);
$searchshapeseven = trim ($searchshapeseven);
$searchshapeeight = trim ($searchshapeeight);
$searchshapenine = trim ($searchshapenine);
$searchshapeten = trim ($searchshapeten);
$searchshapeeleven = trim ($searchshapeeleven);
$searchlab = trim ($searchlab);
$searchlabtwo = trim ($searchlabtwo);
$searchlabthree = trim ($searchlabthree);
$searchlabfour = trim ($searchlabfour);
$searchlabfive = trim ($searchlabfive);
$searchlabsix = trim ($searchlabsix);
$searchlabsix = trim ($searchlabsix);
$colorfrom = trim ($colorfrom);
$colorto = trim ($colorto);
$searchclarityfrom = trim ($searchclarityfrom);
$searchclarityto = trim ($searchclarityto);
$searchpricefrom = trim ($searchpricefrom);
$searchpriceto = trim ($searchpriceto);
$searchcaratsfrom = trim ($searchcaratsfrom);
$searchcaratsto = trim ($searchcaratsto);

/*check if search term was entered*/
/*add slashes to search term*/
if (!get_magic_quotes_gpc())
{
$searcharea = addslashes($searcharea);
$searchshape = addslashes($searchshape);
$searchshapetwo = addslashes($searchshapetwo);
$searchshapethree = addslashes($searchshapethree);
$searchshapefour = addslashes($searchshapefour);
$searchshapefive = addslashes($searchshapefive);
$searchshapesix = addslashes($searchshapesix);
$searchshapeseven = addslashes($searchshapeseven);
$searchshapeeight = addslashes($searchshapeeight);
$searchshapenine = addslashes($searchshapenine);
$searchshapeten = addslashes($searchshapeten);
$searchshapeeleven = addslashes($searchshapeeleven);

$searchlab = addslashes($searchlab);
$searchlabtwo = addslashes($searchlabtwo);
$searchlabthree = addslashes($searchlabthree);
$searchlabfour = addslashes($searchlabfour);
$searchlabfive = addslashes($searchlabfive);
$searchlabsix = addslashes($searchlabsix);

$colorfrom = addslashes($colorfrom);
$colorto = addslashes($colorto);

$searchclarityfrom = addslashes($searchclarityfrom);
$searchclarityto = addslashes($searchclarityto);

$searchpricefrom = addslashes($searchpricefrom);
$searchpriceto = addslashes($searchpriceto);

$searchcaratsfrom = addslashes($searchcaratsfrom);
$searchcaratsto = addslashes($searchcaratsto);

}
if (!empty($searcharea)){
//query for searchlabs
if (!empty($searchlab)){
$querylab = "AND lab like '$searchlab'";
}

if (!empty($searchlabtwo)){
$querylabtwo = "AND lab like '$searchlabtwo'";
}

if (!empty($searchlabthree)){
$querylabthree = "AND lab like '$searchlabthree'";
}

if (!empty($searchlabfour)){
$querylabfour = "AND lab like '$searchlabfour'";
}

if (!empty($searchlabfive)){
$querylabfive = "AND lab like '$searchlabfive'";
}

if (!empty($searchlabsix)){
$querylabsix = "AND lab like '$searchlabsix'";
}
//

//query for searchshape
if (!empty($searchshape)){
$queryshape = "and shape like '$searchshape'";
}

if (!empty($searchshapetwo)){
$queryshapetwo = "and shape like '$searchshapetwo'";
}

if (!empty($searchshapethree)){
$queryshapethree = "AND shape like '$searchshapethree'";
}

if (!empty($searchshapefour)){
$queryshapefour = "AND lab like '$searchshapefour'";
}

if (!empty($searchshapefive)){
$queryshapefive = "AND shape like '$searchshapefive'";
}

if (!empty($searchshapesix)){
$queryshapesix = "AND shape like '$searchshapesix'";
}

if (!empty($searchshapeseven)){
$queryshapeseven = "AND shape like '$searchshapeseven'";
}

if (!empty($searchshapeeight)){
$queryshapeeight = "AND shape like '$searchshapeeight'";
}

if (!empty($searchshapenine)){
$queryshapenine = "AND shape like '$searchshapenine'";
}

if (!empty($searchshapeten)){
$queryshapeten = "AND shape like '$searchshapeten'";
}

if (!empty($searchshapeeleven)){
$queryshapeeleven = "AND shape like '$searchshapeeleven'";
}
//
/* connects to database */
@ $dbconn = new mysqli('MYSQL01', 'websightprojects', 'brow51', 'websightprojects');
if (mysqli_connect_errno())
{
echo 'Error: Could not connect to database. Please try again later.';
exit;
}
}
/*query the database*/
$query = "select * from diamond where area like '$searcharea' $queryshape $queryshapetwo";
$result = $dbconn->query($query);


echo "<table width='60%' border='1' cellspacing='0' bordercolor='#000000'>
<tr><td>ID#</td>";
echo "<td>Shape</td>";
echo "<td>Carat</td>";
echo "<td>Color</td>";
echo "<td>Clarity</td>";
echo "<td>Measurements</td>";
echo "<td>Lab</td>";
echo "<td>Price/ct</td>";
echo "<td>Price</td>";
echo "<td>%Rap</td>";
echo "<td>Details</td> </tr><br>";




//echo '<p>There are :'.$num_results.' results.</p>';/*number of rows found*/
$num_results = $result->num_rows;
/*loops through results*/
for ($i=0; $i <$num_results; $i++)
{
$num_found = $i + 1;
$row = $result->fetch_assoc();

echo "<tr><td>".($row['diamondid'])."</td>";
echo "<td>".($row['shape'])."</td>";
echo "<td>".($row['carat'])."</td>";
echo "<td>".($row['color'])."</td>";
echo "<td>".($row['clarity'])."</td>";
echo "<td>".($row['measurements'])."</span>-<span>".($row['mone'])."</span>x<span>".($row['mtwo'])."</span>mm</td>";
echo "<td>".($row['lab'])."</td>";
echo "<td>".($row['priceCT'])."</td>";
echo "<td>".($row['price'])."</td>";
echo "<td>".($row['rap'])."</td>";
echo "<td>".($row['null'])."<br></td></tr>";

}
/*free database*/

$dbconn->close();
?>

idalatob
05-10-2008, 03:44 PM
Do the following & we will help you straight away:

1.) Echo (&post) the query you get (after filling in the form).
2.) Show the output after running the script.
3.) Show what you expect to get. (by running the script in phpmyadmin for ex.)

WhaLberg
05-11-2008, 06:13 AM
Because your query is not in a while loop.

it is:


$query = "select * from diamond where area like '$searcharea' $queryshape $queryshapetwo";
$result = $dbconn->query($query);
// and the results you want to display..


It must be like this:


$query = "select * from diamond where area like '$searcharea' $queryshape $queryshapetwo";
while ($result = $dbconn->query($query))
{
// and the results you want to display..
}

brow51
05-11-2008, 10:13 PM
I got it to work. I redid my php code and basically created a query string for each value, then check to see if it was selected. Now I just need to figure out how to implement arrange buttons within it.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum