query inside query (insert (select))doesn't work

[Stream]

justsomeguy You are a MANThank you so much that was just what I need !

[Stream]

I was recomended to use the code as bellow to check if one of the condition not selected for example what if somebody will select just Mark=toyota and will leave empty Model and Year.

$where = array();$whereclause = '';if (!empty($mark)) {    $where[] = "a.mark = '$mark'";}if (!empty($model)) {    $where[] = "a.model = '$model'";}if (!empty($year)) {    $where[] = "a.year = $year";}if (count($where > 0)) {    $whereclause = join(' AND ', $where);}

$result = mysql_query("SELECT * FROM `add_new_car` AS a INNER JOIN `image_add_car` AS b ON				 a.id_add_car = b.user_id WHERE (b.user_id, b.id_image_add_car) IN (SELECT user_id, MIN(id_image_add_car)	FROM image_add_car GROUP BY user_id) AND ".$whereclause." ORDER BY a.id_add_car");

Mark=toyota Model=Prado Year=2013But why when I chose just Mark it doesn't gives me any result but at least together with Model=Prado it works without selecting YearThank you

[justsomeguy]

Print out the final query to see what it says. Adding additional where clauses should not show more records, where clauses filter the set of records smaller.

[Stream]

I think the problem is in my query string because when I refresh the search page it gives me an error lik : 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 'ORDER BY a.id_add_car' at line 5

Edited April 26, 2013 by Stream

[justsomeguy]

You'll get that error if nothing was selected, because there is an AND and then an empty where clause variable, and then ORDER BY. It looks like WHERE ... AND ORDER BY

[Stream]

Sorry justsomeguy but what do you mean by saying

Print out the final query to see what it says

$result = mysql_query("SELECT * FROM `add_new_car` AS a INNER JOIN `image_add_car` AS b ON								 a.id_add_car = b.user_id WHERE (b.user_id, b.id_image_add_car) IN (SELECT user_id, MIN(id_image_add_car)	  FROM image_add_car GROUP BY user_id) AND ".$whereclause." ORDER BY a.id_add_car");

the code returns me result just when I select at least Mark and Model

Edited April 26, 2013 by Stream

[justsomeguy]

echo "SELECT * FROM `add_new_car` AS a INNER JOIN `image_add_car` AS b ONa.id_add_car = b.user_id WHERE (b.user_id, b.id_image_add_car) IN (SELECT user_id, MIN(id_image_add_car)FROM image_add_car GROUP BY user_id) AND ".$whereclause." ORDER BY a.id_add_car";

[Stream]

[justsomeguy]

It doesn't look like there's a problem with that.

[Stream]

Thank you Ive just misplased model and mark It's ok now!

[Stream]

$where = array();$whereclause = '';if (!empty($mark)) {    $where[] = "a.mark = '$mark'";}if (!empty($mark)) {    $where[] = "a.model = '$model'";}if (!empty($year)) {    $where[] = "a.year = '$year'";}if (count($where > 0)) {    $whereclause = join(' AND ', $where);}

how to implement else statement in case of $where[] = "a.mark = '$model'"; = 0to show by default "Toyota" I think should be something like else $whereclause = join(' AND ', $where(Toyota)); Thank you

[justsomeguy]

if (!empty($mark)) {    $where[] = "a.mark = '$mark'";}else {    $where[] = "a.mark = 'Toyota'";}

[Stream]

Can you help me please why It doesn't echo "No date like that"; when there is no date like I search.

if($result>0) { while($row = mysql_fetch_array($result))   {  echo "<table border='1' align=center style='width: 750px;'  bordercolor=#CC99FF ><tr><th>Foto</th><th>Type</th><th>Description</th>  </tr>";  echo "<tr>";    echo "<td width='150'> <img src='" .$row['big'] ."' style='width: 150px; height: 100px; ' /></td>";   echo "<td class=search>" ."Mark:        ". htmlentities($row['mark'], ENT_QUOTES)  ."<br>"               ."Model:       ". htmlentities($row['model'], ENT_QUOTES) . "<br>"  ."Year:  ". htmlentities($row['year'], ENT_QUOTES)  . "<br>" ."Price:         ". htmlentities($row['price'], ENT_QUOTES) . "<br>" ."Run:       ". htmlentities($row['run_km'], ENT_QUOTES). "<br>"              ."Color:         ". htmlentities($row['color'], ENT_QUOTES) . "</td>";     echo '<td width="380" class=search><a href="view.php?rmsd='.$row['id_add_car'].'&xlxs=' .$row['id_image_add_car'].'">' .$row['description'].'</a>' ."<br>"."<br>"         ."Name:       "."/ ". htmlentities($row['name'], ENT_QUOTES)   ."/"."<br>" ."Number:      " . htmlentities($row['mobile'], ENT_QUOTES) .'</td>';               echo "</tr>";  echo ("<br />");   } echo "</table>";  }  else  [b]echo "No date like that";[/b] { print mysql_error(); }   mysql_close($connection); ?>

[justsomeguy]

If it's not hitting the else then it sounds like $result is considered to be greater than 0. $result isn't a number though, so I'm not sure why you're checking if it's greater than 0. The return value from mysql_query or mysqli_query is a resource variable, and all resources are considered to evaluate to boolean true if they get casted. Your comparison with 0 is going to cast it to an integer, which would probably end up as 1. So it's always going to be considered greater than 0 since it is a resource.

[Stream]

but what should I change to make it work! Should i write here something like

if($result>0) & if($result not=1)

Thank you

Edited May 20, 2013 by Stream

[justsomeguy]

If you're trying to test if the query returned any records then use the mysql_num_rows function, that's what it's there for.

[Stream]

if(mysql_num_rows($result)>0) 

this is the changes I've done and it worked fine thank you!

Edited May 21, 2013 by Stream

[Stream]

I could find an example for pagination like bellow and like to implement it to my query.

<?phpif (isset($_GET["page"])) { $page  = $_GET["page"]; } else { $page=1; };$start_from = ($page-1) * 20;$sql = "SELECT * FROM students ORDER BY name ASC LIMIT $start_from, 20";$rs_result = mysql_query ($sql,$connection);?><table><tr><td>Name</td><td>Phone</td></tr><?phpwhile ($row = mysql_fetch_assoc($rs_result)) {?>			<tr>			<td><? echo $row["Name"]; ?></td>			<td><? echo $row["PhoneNumber"]; ?></td>			</tr><?php};?></table><?php$sql = "SELECT COUNT(Name) FROM students";$rs_result = mysql_query($sql,$connection);$row = mysql_fetch_row($rs_result);$total_records = $row[0];$total_pages = ceil($total_records / 20); for ($i=1; $i<=$total_pages; $i++) {			echo "<a href='pagination.php?page=".$i."'>".$i."</a> ";};?>

My query is like bellow

if (isset($_GET["page"])) { $page  = $_GET["page"]; } else { $page=1; };$start_from = ($page-1) * 3;$result = mysql_query("SELECT * FROM `add_new_car` AS a INNER JOIN `image_add_car` AS b ON				 a.id_add_car = b.user_id WHERE (b.user_id, b.id_image_add_car) IN (SELECT user_id, MIN(id_image_add_car)	FROM image_add_car GROUP BY user_id) AND ".$whereclause." ORDER BY a.id_add_car desc LIMIT $start_from, 3");

Here in the code I made some changes like bellow

<?php$sql = "SELECT COUNT(*)  FROM {$result}";$rs_result = mysql_query($sql);$row = mysql_fetch_row($rs_result);				  this is the line 1610$total_records = $row[0];$total_pages = ceil($total_records / 3); for ($i=1; $i<=$total_pages; $i++) {			echo "<a href='index.php?page=".$i."'>".$i."</a> ";};mysql_close($connection);?>

but gives me an error message

Warning: mysql_fetch_row() expects parameter 1 to be resource, boolean given in F:\home\je\www\search.php on line 1610

thank you Edited May 21, 2013 by Stream

[justsomeguy]

That means your query failed, you should be using mysql_error to check for errors.

[Stream]

That means your query failed, you should be using mysql_error to check for errors.

it gives me an error like: Table 'jsalon.resource' doesn't existbut jsalon is the name of my database why it sees like table? thank you

[justsomeguy]

It is telling you that the database jsalon does not have a table called resource.