mysql query help

[cmnevada]

Let me explain you the whole set-up.
There are 2 tables which i need to work on for this query--- "proj_cont"
and "contact". proj_cont is a table with only 2 fields pid,cid. which is used
to associate projects with contacts. Now i want to delete all those
associations for which the notes field in contact table is blank(null).
so in effect it should delete all rows in proj_cont table whose cid =
contactid(notes=blank).
When i run this query it gives me the rows i need.
SELECT contact.contactid FROM contact WHERE contact.notes = " "

I am trying to run this query and it is giving me error 1064.
i am new to mysql so can anybody help me with this query or give me another query which will work.

$query= "SELECT contact.contactid FROM contact WHERE contact.notes = " " ";
$getvalue = mysql_query($query) or die(mysql_error());
$num_rows = mysql_num_rows($getvalue);

for($i=0; $i < $num_rows; $i++)
{
$row = mysql_fetch_array($getvalue);
DELETE * FROM proj_cont WHERE cid = "$row[i]";

}

[dniwebdesign]

Try this, no guarentees but worth a shot...

$query= "SELECT contact.contactid FROM contact WHERE contact.notes = '' ";
$getvalue = mysql_query($query) or die(mysql_error());
$num_rows = mysql_num_rows($getvalue);

for($i=0; $i < $num_rows; $i++)
{
$row = mysql_fetch_array($getvalue);
DELETE * FROM proj_cont WHERE cid = "$row[i]";

}

or if the above doesn't work try

I am trying to run this query and it is giving me error 1064.
i am new to mysql so can anybody help me with this query or give me another query which will work.

$query= "SELECT contact.contactid FROM contact WHERE contact.notes = ' ' ";
$getvalue = mysql_query($query) or die(mysql_error());
$num_rows = mysql_num_rows($getvalue);

for($i=0; $i < $num_rows; $i++)
{
$row = mysql_fetch_array($getvalue);
DELETE * FROM proj_cont WHERE cid = "$row[i]";

}

[cmnevada]

I am trying to run this query in mysql admin and its not working. How do i run a php code. do i have to connect to a database and then write the query??



Try this, no guarentees but worth a shot...

$query= "SELECT contact.contactid FROM contact WHERE contact.notes = '' ";
$getvalue = mysql_query($query) or die(mysql_error());
$num_rows = mysql_num_rows($getvalue);

for($i=0; $i < $num_rows; $i++)
{
$row = mysql_fetch_array($getvalue);
DELETE * FROM proj_cont WHERE cid = "$row[i]";

}

or if the above doesn't work try

I am trying to run this query and it is giving me error 1064.
i am new to mysql so can anybody help me with this query or give me another query which will work.

$query= "SELECT contact.contactid FROM contact WHERE contact.notes = ' ' ";
$getvalue = mysql_query($query) or die(mysql_error());
$num_rows = mysql_num_rows($getvalue);

for($i=0; $i < $num_rows; $i++)
{
$row = mysql_fetch_array($getvalue);
DELETE * FROM proj_cont WHERE cid = "$row[i]";

}

[dniwebdesign]

Yes, if running a php code your server needs to support PHP. You then have to connect to a database and then you can query it.

I don't know if running your code will work directly as I don't know if it can reconize the for loops, etc... as they are PHP codes.

Make sure your codes are surrounded like:

<?php

// YOUR PHP CODE HERE

?>

[raf]

forget all the above since
for($i=0; $i < $num_rows; $i++)
{
$row = mysql_fetch_array($getvalue);
DELETE * FROM proj_cont WHERE cid = "$row[i]";

}
is completely wrong.

The fundamental sollution is to set up an ON DELETE CASCADE http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html
which will then automatically solve qour problem by removin the master tables row.

If you can't remove the row from the master-table or if for some reason you don't want the cascade and need to do it in PHP, then you need

//your connection
$link = @mysql_connect('localhost', 'mysql_user', 'mysql_password') or die ('Not connected : ' . mysql_error());
$db_selected = @mysql_select_db('db-name', $link) or die ('Can\'t use foo : ' . mysql_error());

$query= "SELECT contact.contactid FROM contact WHERE contact.notes = Null"; // ' ' is something completely different --> it's a space
$result = mysql_query($query, $link) or die(mysql_error());
if (mysql_num_rows($result)>=1){
while ($row=mysql_fetch_assoc($result)){
$sql = "DELETE FROM proj_cont WHERE cid = " . $row['contactid'];
$result = mysql_query($sql, $link) or die(mysql_error());
}
} else {
echo 'No rows found with a blank notes-field.';
}


you need to change 'mysql_user', 'mysql_password' and 'db-name' to your mysql-useraccount details and db-name

[cmnevada]

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/virtual/site3/fst/var/www/html/stonewear/pdftest.php on line 12
I am getting this error

[dniwebdesign]

It is probably because you aren't connecting or selecting the tables correctly.

[raf]

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/virtual/site3/fst/var/www/html/stonewear/pdftest.php on line 12
I am getting this error
My bet is that the returned recordset is empty, or not existing (for instance because you don't have a resourceID with that name.

But if you don't post the code you use (at least the first 12 lines), then i don't see what we can do for you :rolleyes: