[SOLVED] PHP and MySQL error

[john010117]

Does anyone know what's wrong with this?

 

<?php
$query = "DELETE FROM
                  $privmsgs_tbl_name AS pm,
                  $privmsgs_spec_tbl_name AS pm_spec
              WHERE
                  pm.msg_id = $pm_id
              AND pm.recipient_uid = $session_uid
              AND pm_spec.msg_id = $pm_id
              AND pm_spec.user_id = $session_uid
              AND pm.msg_id = pm_spec.msg_id
              AND pm.recipient_uid = pm_spec.user_id
              AND pm.author_uid = author_id";
$result = mysql_query($query) OR DIE (mysql_error());
?>

 

MySQL keeps on giving me this error:

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 'WHERE pm.msg_id = '3' AND pm.recipient_uid = '4' A' at line 4

 

I checked the manual and I believe I'm doing this correctly... but apparently, I'm not. Help, please. Oh, and btw, I got all the variables defined, so that's not the problem.

[corillo181]

are you deleting from 2 table?

[john010117]

Yes.

[corillo181]

you need to have 2 id's that meet in both table and just do something like this

 

DELETE a, b FROM a LEFT JOIN b ON b.id=a.id;

[skali]

Try printing the query and then run the printed query in mysql client or phpmyadmin, this will ensure that the php variables are getting populated properly.

[john010117]

[b]@ corillo181:[/b]
So then, it would look something like this?

[code]
<?php
$query = "DELETE
                  $privmsgs_tbl_name,
                  $privmsgs_spec_tbl_name
              FROM
                  $privmsgs_tbl_name
              LEFT JOIN
                  $privmsgs_spec_tbl_name
              ON
                  $privmsgs_tbl_name . '.msg_id' = $privmsgs_spec_tbl_name . '.msg_id'
              WHERE
                  pm.recipient_uid = $session_uid
              AND pm_spec.msg_id = $pm_id
              AND pm_spec.user_id = $session_uid
              AND pm.msg_id = pm_spec.msg_id
              AND pm.recipient_uid = pm_spec.user_id
              AND pm.author_uid = author_id";
$result = mysql_query($query) OR DIE (mysql_error());
?>

 

@ skali:

The printed query looks like this:

DELETE FROM privmsgs AS pm, privmsgs_spec AS pm_spec WHERE pm.msg_id = '3' AND pm.recipient_uid = '4' AND pm_spec.msg_id = '3' AND pm_spec.user_id = '4' AND pm.msg_id = pm_spec.msg_id AND pm.recipient_uid = pm_spec.user_id AND pm.author_uid = author_id

 

So the variables are all there. It fails in phpMyAdmin also.[/code]

[john010117]

I figured it out.

 

<?php
$query = "DELETE
                  $privmsgs_tbl_name,
                  $privmsgs_spec_tbl_name
              FROM
                  $privmsgs_tbl_name,
                  $privmsgs_spec_tbl_name
              WHERE
                  $privmsgs_tbl_name.msg_id = '$pm_id'
                  AND $privmsgs_tbl_name.recipient_uid = '$session_uid'
                  AND $privmsgs_spec_tbl_name.msg_id = '$pm_id'
                  AND $privmsgs_spec_tbl_name.user_id = '$session_uid'
                  AND $privmsgs_tbl_name.msg_id = $privmsgs_spec_tbl_name.msg_id
                  AND $privmsgs_tbl_name.recipient_uid = $privmsgs_spec_tbl_name.user_id
                  AND $privmsgs_tbl_name.author_uid = $privmsgs_spec_tbl_name.author_id";
$result = mysql_query($query) OR DIE (mysql_error());
?>

 

Thank you to everyone who helped.