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

[Stream]

Thanks for people who create this site because i could get lat's of help here ! But recently met a new problem with insert and select which doesn't work for meThe query looks like this

$sql = "Insert into image_add_car (user_id,ap_id,big,small) VALUES ('SELECT id_add_car FROM add_new_car WHERE date=NOW() ORDER BY id_add_car,date DESC LIMIT 1','2','$filename','$filename1')";mysql_query($sql);

but insted of id from id_add_car in the field of user_id i can see just 'SELECT id_add_car FROM add_new_car WHERE date=NOW() ORDER BY id_add_car,date DESC LIMIT 1'

[fikiwan]

edit... sorry idont knew..

Edited March 11, 2013 by fikiwan

[Mudsaf]

The insert works fine, i tried with phpMyAdmin query window and worked. Check your mysql field length + etc. Also you could check error code.

mysql_query($sql) or die(mysql_error());

This is the order you're adding data user_id,ap_id,big,small 'SELECT id_add_car FROM add_new_car WHERE date=NOW() ORDER BY id_add_car,date DESC LIMIT 1','2','$filename','$filename1'

Edited March 11, 2013 by Mudsaf

[JamesB]

Try removing the single quotes from the select query, and adding a set of () brackets around it instead.It's probably being treated as a string.

[Stream]

Try removing the single quotes from the select query, and adding a set of () brackets around it instead.It's probably being treated as a string.

$sql = "Insert into image_add_car (user_id,ap_id,big,small) VALUES ((SELECT id_add_car FROM add_new_car WHERE date=NOW() ORDER BY id_add_car,date DESC LIMIT 1),'2','$filename','$filename1')";mysql_query($sql);

I ve done it as you told me but it gives the same thing. to Mudsaf It gives no erros

Edited March 11, 2013 by Stream

[justsomeguy]

Insert into image_add_car (user_id,ap_id,big,small) SELECT id_add_car, '2', '$filename', '$filename1' FROM add_new_car WHERE date=NOW() ORDER BY id_add_car,date DESC LIMIT 1 There's usually a better way to get the ID that you just inserted though. mysql_insert_id will return the autogenerated ID from the previous insert query. http://www.php.net/manual/en/function.mysql-insert-id.php

[Stream]

Thank you so much I will check it now

[Stream]

Can you tell me please why when I am trying to get the ID by the way like it's shows me nothing $sql = mysql_query("SELECT id_add_car,model,date FROM add_new_car WHERE model='$model' and date=NOW() ORDER BY id_add_car,date DESC LIMIT 1");$maxrowid = mysql_fetch_array($sql);$id_insert = $maxrowid['id_add_car']; echo "<br /><br /><h2><b>".$id_insert."</b></h2><br /><br />";

[justsomeguy]

If there's nothing there then the query isn't returning any rows. If you're not seeing an error message about that then you have error messages disabled.

[Stream]

What if it gives me an error like Last inserted record has id 0$sql = mysql_query("SELECT id_add_car,model,date FROM add_new_car WHERE model='$model' and date=NOW() ORDER BY id_add_car,date DESC LIMIT 1");$maxrowid = mysql_fetch_array($sql);$id_insert = $maxrowid['id_add_car']; echo "<br /><br /><h2><b>".$id_insert."</b></h2><br /><br />";$sql ="Insert into image_add_car (user_id,ap_id,big,small) VALUES (LAST_INSERT_ID(),'2','$filename','$filename1')"; printf ("Last inserted record has id %d\n", mysql_insert_id()); mysql_query($sql) or die(mysql_error());

Edited March 12, 2013 by Stream

[justsomeguy]

Don't use LAST_INSERT_ID in an insert query, you don't want to insert a new row using the last ID that was added, you want a new ID.

[Stream]

The error Last inserted record has id 0 happens when i am putting $sql = mysql_query("SELECT id_add_car,model,date FROM add_new_car WHERE model='$model' and date=NOW() ORDER BY id_add_car,date DESC LIMIT 1");$maxrowid = mysql_fetch_array($sql);$id_insert = $maxrowid['id_add_car']; echo "<br /><br /><h2><b>".$id_insert."</b></h2><br /><br />";

[justsomeguy]

Yeah, because you're probably pulling a record that actually has an ID of 0, because when you're inserting records you're telling it to use the last ID as the ID of the new record. If the table doesn't have any records, what is the last ID going to be set to? It's going to return 0, there is no last ID. If you're using an autoincrement column you just leave that column out of the insert query, it will generate an ID automatically. And then you can use mysql_insert_id to get the previous ID instead of your SELECT query.

[Stream]

I have changed the query string like bellow and it worked as I needed ! $sql = mysql_query("SELECT id_add_car,model,date FROM add_new_car"); but I got a question what if a few user at once will enter date Will it work correct? Thank you

Edited March 14, 2013 by Stream

[Stream]

I have changed the query string like bellow and it worked as I needed ! $sql = mysql_query(SELECT id_add_car,model,date FROM add_new_car); 424---308---images/1363209424.jpg423---308---images/1363209421.jpg422---308---images/1363209418.jpg421---308---images/1363209416.jpg but I got a question what if a few user at once will enter date Will it work correct? Thank you

Edited March 14, 2013 by Stream

[justsomeguy]

They will insert correctly. If you're still using a select query to get the largest ID then that may be a problem if several people do it at the same time, there might be a race condition that would cause one of them to get the ID for someone else. That's why I suggested using mysql_insert_id to get the last auto-generated ID for that connection. That will work no matter how many people are using it.

[Stream]

Thank you so much for help I will try to use the way you told me to do :-)

[Stream]

This is the code using which I am selecting date from two table first add_new_car keeps date and the second image_add_car keeps image path(like shown bellow)

$result = mysql_query("SELECT * FROM `add_new_car`,`image_add_car` where id_add_car = user_id and mark='$model' and model='$mark' and year='$year'ORDER BY image_id_add_car");

id_image_add_car-------user_id 602 --------- 386 2 images/1365172947.jpg images/small_1365172947.jpg 601 --------- 386 2 images/1365172945.jpg images/small_1365172945.jpg 600 --------- 386 2 images/1365172942.jpg images/small_1365172942.jpg 599 --------- 386 2 images/1365172940.jpg images/small_1365172940.jpg 598 --------- 384 2 images/1365172691.jpg images/small_1365172691.jpg 597 --------- 384 2 images/1365172688.jpg images/small_1365172688.jpg 596 --------- 384 2 images/1365172685.jpg images/small_1365172685.jpg 595 --------- 384 2 images/1365172683.jpg images/small_1365172683.jpg 594 --------- 384 2 images/1365172679.jpg images/small_1365172679.jpg 593 --------- 384 2 images/1365172677.jpg images/small_1365172677.jpg

And I can get something like shown bellow it shows the picture and date but insted of one the whole amount of user_id for example (386) 4 of them then it echo another date with his pictures and so on What's the way to limit amount of user_idCan you help me here

[justsomeguy]

Set a join condition on your tables, you're just doing a full inner join which returns a result table of every possible row combination from the other two tables you're joining. SELECT * FROM `add_new_car` AS a INNER JOIN `image_add_car` AS b ON a.id_add_car = b.user_id WHERE ... I might have listed the wrong tables for those columns, but you should explicitly specify the join and the condition.

[Stream]

Greate thanks my dear friend ! I will try it!

[Stream]

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

I am sorry but this gives me the same result

Edited April 17, 2013 by Stream

[justsomeguy]

What result are you expecting? The rows that it is returning are different, if you want it to return only one of those rows then how does it know which one you want?

[Stream]

is it possible to include also id_mage_add_car to make a selection or make a limitation?For example id_image_add_car min 657 and max 664 I need to show just min 657 is it possible Thanks in advance

Edited April 18, 2013 by Stream

[justsomeguy]

I think there's probably a way to do that with a subquery although I'm having a hard time working out the syntax, because the subquery needs to include the current user ID from the join. I'm thinking about something like this: SELECT * FROM `add_new_car` AS a INNER JOIN `image_add_car` AS b ON a.id_add_car = b.user_id WHERE b.id_image_add_car IN (SELECT id_image_add_car FROM image_add_car WHERE user_id=b.user_id ORDER BY id_image_add_car DESC LIMIT 1) AND a.mark='$model' and a.model='$mark' and a.year='$year' ORDER BY a.id_add_car The problem with that is the b.user_id in the subquery, I'm not sure if it will use the right ID for that.

[justsomeguy]

This might work: 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