Select Help

[adamnichols45]

Can any one tel me whats wrong withi this code please

$sql = mysql_query("SELECT make, you, size FROM tablename WHERE make ='$carrymake' and location = 'loc' size= '$size and price >= '$miniprice' <= '$maxiprice' LIMIT $from, $max_results");

[dreamcatcher]

Looks like you are missing some clauses. Nothing before 'size' for example. Also, one missing after '$miniprice'.

Why, are you getting specific errors? Use mysql_error() in your query for some help.

$sql = mysql_query("SELECT make, you, size FROM tablename WHERE make ='$carrymake' and location = 'loc' size= '$size and price >= '$miniprice' <= '$maxiprice' LIMIT $from, $max_results") or die(mysql_error());

[adamnichols45]

no error its just not giving me back the results i was hoping for

[adamnichols45]

maybe this is clearer to understand

and price >= '$miniprice' and price <= '$maxiprice'

i just want to return result that is = to or greater than $miniprice and < or = to $maxiprice

what is the correct code for this please as mine does not return any results

thanks

[dreamcatcher]

$sql = mysql_query("SELECT make, you, size FROM tablename WHERE make ='$carrymake' AND location = 'loc' AND size= '$size AND price >= '$miniprice' AND price <= '$maxiprice' LIMIT $from, $max_results") or die(mysql_error());

Try that. Like I say, you were missing some AND clauses. I have capitalized them so you can see.

[adamnichols45]

I am confused with the order by clause

i want the numbers to be order small to big ie

5
300
500
5000
20000

How would i do this please

[edited by: adamnichols45 at 10:31 pm (utc) on Jan. 24, 2005]

[dreamcatcher]

How are you using the order by clause? If you want it to order by price from smalled to largest, it will do this by default. Just use:

...AND price <= '$maxiprice' LIMIT $from, $max_results ORDER BY price") or die(mysql_error());

This is the same as ORDER BY price ASC

Hope that helps.

[adamnichols45]

does it matter if the order by is before or after the LIMIT clause as this just creates an eror when i put it after the LIMIT clause alos this does not work when it is before the Limit - I still get muddled Results

[dreamcatcher]

Oops, sorry about that should be BEFORE the limit clause.

What sort of data are you getting returned?

[adamnichols45]

im getting highest number first then muddled results-

maybe this is useful to knwo also that its also in conjunction with pagination

so i have two select methods ie

$sql = mysql_query("SELECT make, model, price, year2, app, reg, photodesc, id, size FROM cars WHERE make ='$carrymake' and model ='$carrymodal' and price >=$miniprice and price <=$maxiprice order by price LIMIT $from, $max_results");

THEN

$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM cars WHERE make ='$carrymake' and model ='$carrymodal' and price >=$miniprice and price <=$maxiprice order by price"),0);

[adamnichols45]

Any one still help out with this one please ORDER BY

[dreamcatcher]

You are missing some single quotes from your second query. $miniprice and $maxiprice should be enclosed in single quotes:

$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM cars WHERE make ='$carrymake' and model ='$carrymodal' and price >='$miniprice' and price <='$maxiprice' order by price"),0);

Does this make any difference?

[adamnichols45]

no difference -

i have sicne removed these if it helps im trying to eliminate possible errors..

this is now my select statment

$sql = mysql_query("SELECT make, model, price, year2, app, reg, photodesc, id, size FROM cars WHERE make ='$carrymake' and model ='$carrymodal' Order BY price LIMIT $from, $max_results");

Whats wrong with this? is it maybe the way im displaying the data ie like this

if(mysql_num_rows($sql) == 0){
print 'Sorry no matches found.';

} else {
while($row = mysql_fetch_array($sql)){
$make = $row['make'];
$model = $row['model'];
$reg = $row['reg'];
$year2 = $row['year2'];
$size = $row['size'];
$tab = '';
$tab .=<<<EOD
<table width='570' border='0'class=setb>
<tr>
<td height=50 width=220 height="130" bgcolor=EDEDED class=sehead><a href="#" onClick="window.open('search2.php?id=$idshow','mywindow','width=560,height=400')">$year2 $aaa $bbb $size</a></td>
<td valign="middle" rowspan="2" bgcolor=EDEDED width="70" class=blackb >$price</td>
</tr>
<tr>
<td width=380 bgcolor=EDEDED class=fs>$ddd&nbsp;<img src="images/$app" alt="" border="0"><br><br></td>
</tr>
</table>
EOD;
print $tab;
}

[adamnichols45]

can any one see why the code im using is not displaying it in order of smallest price to largest price please

$sql = mysql_query("SELECT make, model, price, year2, app, reg, photodesc, id, size FROM cars WHERE make ='$carrymake' and model ='$carrymodal' Order BY price LIMIT $from, $max_results");

[tomda]

Your query seems OK to me.

If you have phpmyadmin, than just echo your query (echo $sql), copy it and paste it in the sql-box in Phpmyadmin.

Surely, this will give the problem to your query.

[adamnichols45]

Yes the whole code is ok BUT the price is not being displayed smallest to largest i just want to know why?

[adamnichols45]

problem was sorted thanks to those who posted answer was to change the field type from varchar to int

thanks to rover who suggested that