Jump to content

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


Stream
 Share

Recommended Posts

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

Link to comment
Share on other sites

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 by Stream
Link to comment
Share on other sites

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 by Stream
Link to comment
Share on other sites

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";

Link to comment
Share on other sites

  • 2 weeks later...
$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

Link to comment
Share on other sites

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); ?>

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Stream
Link to comment
Share on other sites

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
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...