join two table and get just one image path

[streamland]

The first picture is date table (add_table)

second table (image_path_table) here I keep images path 

 

 

Please help built the query to select date from (add_table) and path to picture from  (image_path_table) 

$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 a.id_add_car =b.user_id and a.mark='$model' and a.model='$mark' and a.year='$year' ORDER BY a.id_add_car");

thank you 

[Barand]

You do not need

 

a.id_add_car =b.user_id

 

in the WHERE clause - you already specified that in the JOIN ON

 

If you only want a single image (instead of all 8 ) you could GROUP BY a.id_add_car

Edited April 18, 2013 by Barand

[streamland]

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 a.mark='$model' and a.model='$mark' and a.year='$year' ORDER BY a.id_add_car

Thank you so much brand

I Could get something like this and will test it

[Barand]

If that doesn't work, try

SELECT * 
FROM `add_new_car` AS a 
INNER JOIN `image_add_car` AS b ON a.id_add_car = b.user_id 
WHERE a.mark='$mark' and a.model='$model' and a.year='$year' 
GROUP BY a.id_add_car

[streamland]

Barand sorry but this query doesn't work it returs no result

SELECT * 
FROM `add_new_car` AS a 
INNER JOIN `image_add_car` AS b ON a.id_add_car = b.user_id 
WHERE a.mark='$mark' and a.model='$model' and a.year='$year' 
GROUP BY a.id_add_car

anyway the first query worked fine for me.

 

But I've got another question, what if some one will not select or select just a.mark='$mark' should I use

 

IF Statment to build a few query for example like

if empty($model)& empty($year)

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 a.mark='$mark'  ORDER BY a.id_add_car

Thank you!

Edited April 19, 2013 by streamland

[Barand]

Barand sorry but this query doesn't work it returs no result

Probably because you had $mark and $model reversed in your query.

 

 

 

If the user doesn't specify mark or model then leave them out of the WHERE clause EG

$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 = 'WHERE ' . join(' AND ', $where);
}
$query = "SELECT * 
FROM `add_new_car` AS a 
INNER JOIN `image_add_car` AS b ON a.id_add_car = b.user_id 
$whereclause
GROUP BY a.id_add_car";

[streamland]

Probably because you had $mark and $model reversed in your query.

 

 

that was my carelessness the query from you worked fine thank you 

 

SELECT *

FROM `add_new_car` AS a

INNER JOIN `image_add_car` AS b ON a.id_add_car = b.user_id

WHERE a.mark='$mark' and a.model='$model' and a.year='$year'

GROUP BY a.id_add_car

 

now will check WHERE clause :-)

[streamland]

$where = array();
$whereclause = '';

if (!empty($mark)) {
    $where[] = "a.mark = '$model'";
}
if (!empty($model)) {
    $where[] = "a.model = '$mark'";
}
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");

Barand

Mark=toyota Model=Prado Year=2013

But why when I chose just Mark it doesn't gives any result but both together with Model=Prado it works fine

Edited April 22, 2013 by streamland

[Barand]

 

 

$where[] = "a.mark = '$model'";

 

 

$where[] = "a.model = '$mark'";

 

Is it a deliberate obfuscation ploy of yours to to put the mark value in $model and the model value in $mark?

 

All your queries seem to have it like that.

[streamland]

 
Is it a deliberate obfuscation ploy of yours to to put the mark value in $model and the model value in $mark?
 
All your queries seem to have it like that.

Yes ...