...

View Full Version : Delete rows from multiple tables



Foster
11-19-2011, 05:57 PM
i dont know why nothing i write works first time. I am trying to delete from 4 tables based on a name, for some reason it's not deleting anything. my code is below. Please someone help me.


<?php

// connect to the database
include("config.php");

// connect to the mysql server
$link = mysql_connect($server, $db_user, $db_pass)
or die ("Could not connect to mysql because ".mysql_error());

// select the database
mysql_select_db($database)
or die ("Could not select database because ".mysql_error());

// delete the entry
$delete = mysql_query("FROM users, properties,
userProperties, userStats WHERE name = '".$_POST['name']."'");
$res= mysql_query($delete);


?>

tangoforce
11-19-2011, 06:22 PM
You need to use 4 seperate delete queries. AFAIK you can't name 4 seperate tables in one delete query.

Also.. things often don't work first time in programming. Thats why its called learning. When things start to work first time then learning changes its name to debugging. In other words very few programmers write code that works perfectly first time so you're not to start be too hard on yourself ok? :thumbsup:

Foster
11-19-2011, 06:44 PM
thanks, its reassuring to know its not just me.

is there anychance you can give an idea as to how it should look?

tangoforce
11-19-2011, 07:05 PM
As said, 4 different delete queries.



//Sanitize the $_POST name so its safe
$Name = mysql_real_escape_string($_POST['name']);

// delete the entries
mysql_query("FROM users WHERE name = '$Name'");
mysql_query("FROM properties WHERE name = '$Name'");
mysql_query("FROM userProperties WHERE name = '$Name'");
mysql_query("FROM userStats WHERE name = '$Name'");

Foster
11-19-2011, 08:10 PM
hi, thanks for your help. unfortunately it's still not working , this is how my code looks now.


<?php

// connect to the database
include("config.php");

// connect to the mysql server
$link = mysql_connect($server, $db_user, $db_pass)
or die ("Could not connect to mysql because ".mysql_error());

// select the database
mysql_select_db($database)
or die ("Could not select database because ".mysql_error());

//Sanitize the $_POST name so its safe
$Name = mysql_real_escape_string($_POST['name']);

// delete the entries
mysql_query("FROM users WHERE name = '$name'");
mysql_query("FROM properties WHERE name = '$name'");
mysql_query("FROM userProperties WHERE name = '$name'");
mysql_query("FROM userStats WHERE name = '$name'");
$res= mysql_query($delete);


?>

Adee
11-19-2011, 08:11 PM
things often don't work first time in programming. Thats why its called learning.

i thought it was called debugging lol:D

kbluhm
11-19-2011, 08:45 PM
hi, thanks for your help. unfortunately it's still not working , this is how my code looks now.

1. Variables are case-sensitive
2. It's `DELETE FROM ...`
3. Nowhere is $delete defined, not sure why you're including that last line.

Foster
11-19-2011, 08:52 PM
ok code now looks like this and is still not working.


<?php

// connect to the database
include("config.php");

// connect to the mysql server
$link = mysql_connect($server, $db_user, $db_pass)
or die ("Could not connect to mysql because ".mysql_error());

// select the database
mysql_select_db($database)
or die ("Could not select database because ".mysql_error());

//Sanitize the $_POST name so its safe
$name = mysql_real_escape_string($_POST['name']);

// delete the entries
mysql_query("DELETE FROM users WHERE name = '$name'");
mysql_query("DELETE FROM properties WHERE name = '$name'");
mysql_query("DELETE FROM userProperties WHERE name = '$name'");
mysql_query("DELETE FROM userStats WHERE name = '$name'");



?>

the variables are the same case on here as in the database

kbluhm
11-19-2011, 09:07 PM
For future reference, the case between PHP and the database makes no difference.

I was referring to $Name and $name, which you have fixed.

Are you seeing any errors? Are there rows in the database that should be deleted but are not? A little direction would help as opposed to just telling us it's not working.

Foster
11-19-2011, 09:13 PM
i'm not gettting any errors and it's simply not deleting anything from the tables. sorry i should have given more info

tangoforce
11-19-2011, 09:19 PM
Ok, so print out the value of mysql_error() then.

If that reveals nothing then try selecting a row by the value of $name and see what is returned.

Foster
11-19-2011, 09:39 PM
not getting any error's from it, think i might have to give up. thanks for all your help though

Adee
11-19-2011, 10:10 PM
For future reference it would be a lot easier if each user had a unique ID so there wouldn't be any confusion between tables..

Try going into your database and manually executing all of those queries within the same SQL statement delimited by ';' and see if you get any errors.

Foster
11-19-2011, 10:16 PM
ok ran it in phpmyadmin and get the following error

#1064 - 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 'mysql_query("DELETE FROM users WHERE name = '$name'")' at line 1

kbluhm
11-19-2011, 10:43 PM
??? You cannot interpolate PHP within pure SQL queries.

In your latest code, change the first mysql_query to exit.

So instead of:

mysql_query("DELETE FROM users WHERE name = '$name'");
...you will have:

exit("DELETE FROM users WHERE name = '$name'");

Then copy/paste the output here.

I am thinking $name does not contain the value you're expecting. That has to be it, everything else looks correct.

tangoforce
11-20-2011, 12:05 AM
think i might have to give up.

What? - You're not serious :confused:

Trust me, I've given you sound advice. All you need is to practice and get to know what you're doing and you'll be fine. PHP and mysql can seem daunting to start with but once you get to know them you'll look back on this and wonder why you got so stressed about it. Trust me, I did.

If you're really that stuck then as long as its within an hour of this reply download TeamViewer (google) and PM me your id and password. I'll connect to your desktop and get it working for you.

FYI running that query through phpmyadmin won't work - it won't know what '$name' is supposed to be so it will quite literally look for '$name' in the db.

Foster
11-20-2011, 08:51 AM
this is the error i get with your changes

#1064 - 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 'exit("DELETE FROM `users` WHERE name = '$name'")' at line 1

i also tried running it with a name from the database in it instead of $name

Adee
11-20-2011, 08:58 AM
omg.. I'm not trying to talk **** but seriously if you can't grasp a simple concept why are you even trying to learn programming? You DON'T put '$name' in the query in phpMyAdmin. You create a test user and then use that person's name when you're testing the 4 separate delete queries.. It might be best to turn the project over to someone a little more.. able.

Foster
11-20-2011, 09:03 AM
omg if you cant read before you reply then dont reply. did you not see that like i said i ran it with an actual name from the data base? if your not going to help then dont reply

Adee
11-20-2011, 09:17 AM
omg if you cant read before you reply then dont reply. did you not see that like i said i ran it with an actual name from the data base? if your not going to help then dont reply

if 'name' is the column in the users table in which usernames are stored then you should not have any problems.

Foster
11-20-2011, 09:20 AM
'name' is the column in the users table in which usernames are stored but i still get the syntax error

Adee
11-20-2011, 09:35 AM
choose a random username from the database.

go to the sql tab, and put DELETE FROM `users` WHERE name = 'thatname'

where thatname is the name you just chose.

Foster
11-20-2011, 09:40 AM
that worked and deleted the user i selected

Adee
11-20-2011, 09:43 AM
that worked and deleted the user i selected

ok, that's what you need to be doing.

in your original script, make $name = $_POST['name'] or whatever method you have of getting the username to delete.

Then, you want to do
mysql_query("DELETE FROM table WHERE name = '$name'");
mysql_query("DELETE FROM table2 WHERE name = '$name'");

etc..

Foster
11-20-2011, 09:52 AM
that just puts me back where i was in post #8 and it wasnt working then or now

Adee
11-20-2011, 10:25 AM
there is no reason it shouldn't work.. if you are positive that $_POST['name'] is actually a username it should work.

Foster
11-20-2011, 10:33 AM
i'm certain that $_POST['name'] is actually a username. i know you say it should work but for some reason it isnt. you mentioned in an earlier post about unique ID's would it help if each user had a one?

Adee
11-20-2011, 11:39 AM
ya..

it's like, when you first create your database.. say you have id, name, password, email

id is an autonumber field, so once the first user is created they have id of 1, next use id 2, so on and so forth.. but for this example:

if you use only one query with your $_POST['name'] value does it work or does it only not work when you use multiple delete queries?


also you're actually submitting the form, right? you can't just have that page with $name = mysql_escape_string($_POST.. etc and then all of the queries..

Foster
11-20-2011, 11:50 AM
this is the page as i just submitted it and it still didnt work


<?php

// connect to the database
include("config.php");

// connect to the mysql server
$link = mysql_connect($server, $db_user, $db_pass)
or die ("Could not connect to mysql because ".mysql_error());

// select the database
mysql_select_db($database)
or die ("Could not select database because ".mysql_error());

//Sanitize the $_POST name so its safe
$name = mysql_real_escape_string($_POST['name']);

// delete the entries
mysql_query("DELETE FROM users WHERE name = '$name'");


?>

Adee
11-20-2011, 11:55 AM
Make a page called test.php
put in it



<form method='post' action='delete.php'>
name: <input type='text' name='name' /><br />
<input type='submit' name='del' value='Delete' />
</form>


rename the file you just posted to 'delete.php'

change the mysql_query line to




$query = "DELETE FROM `users` WHERE name = '$name'";

if(mysql_query($query))
{
echo "Query executed successfully.";
}


So it should all look like this..



<?php

// connect to the database
include("config.php");

// connect to the mysql server
$link = mysql_connect($server, $db_user, $db_pass)
or die ("Could not connect to mysql because ".mysql_error());

// select the database
mysql_select_db($database)
or die ("Could not select database because ".mysql_error());

//Sanitize the $_POST name so its safe
$name = mysql_real_escape_string($_POST['name']);

// delete the entries
$query = "DELETE FROM `users` WHERE name = '$name'";

if(mysql_query($query))
{
echo "Query executed successfully.";
}


?>

Foster
11-20-2011, 12:00 PM
ok that seemed to do it.

Adee
11-20-2011, 12:03 PM
ok that seemed to do it.

Cool.. now compare the mini-form I gave you and the form you're using to process the data.. you should be able to see errors you made and then change your script to work with multiple queries. If you've gotten it to work with the instructions I gave you, the rest shouldn't be as difficult.

Foster
11-20-2011, 12:35 PM
sorry my brain wont work, i'm getting no where with it

tangoforce
11-20-2011, 01:15 PM
1. Variables are case-sensitive
2. It's `DELETE FROM ...`
3. Nowhere is $delete defined, not sure why you're including that last line.

No idea how I missed out delete when I replied with code earlier.. :eek:

Foster
11-20-2011, 01:17 PM
adee's code works to delete from 1 table i just cant get it to do from several so i'm gonna leave it a while

tangoforce
11-20-2011, 01:25 PM
No need to leave it a while.. just a few hours.

Download TeamViewer (as I've said) and I'll fix it for you this evening.

Secondly even though you're seeing the deleted message on Adees code, have you checked the data in phpmyadmin has actually been wiped? - It's possible the SQL is correctly formatted but still not deleting anything. If thats the case, then you'll still see the message as the SQL correctly executes and returns a true result.

You need to use mysql_affected_rows() in the code too.

Foster
11-20-2011, 01:28 PM
no it actually deletes it from the database, i've checked in phpmyadmin to be sure

tangoforce
11-20-2011, 03:04 PM
Maybe so but you still need to use mysql_affected_rows to be sure otherwise if it does fail to delete a record in the future you'll still see that same message. mysql_query() returns a true or false - false only if the SQL didn't run correctly eg if it had an error. Otherwise it will return true even if it didn't delete anything.

I've a funny feeling that the $name value isn't being set to what you think it is when you try to run it through your normal code. Have you actually printed it to the page to be sure?

kbluhm
11-20-2011, 04:39 PM
this is the error i get with your changes

#1064 - 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 'exit("DELETE FROM `users` WHERE name = '$name'")' at line 1

i also tried running it with a name from the database in it instead of $name

No no no... you should be running that from the script, but it appears you pasted it into phpMyAdmin?

kbluhm
11-20-2011, 04:40 PM
I've a funny feeling that the $name value isn't being set to what you think it is when you try to run it through your normal code. Have you actually printed it to the page to be sure?

Already asked him to try that, but he insisted on pasting the PHP code into phpMyAdmin.


??? You cannot interpolate PHP within pure SQL queries.

In your latest code, change the first mysql_query to exit.

So instead of:

mysql_query("DELETE FROM users WHERE name = '$name'");
...you will have:

exit("DELETE FROM users WHERE name = '$name'");

Then copy/paste the output here.

I am thinking $name does not contain the value you're expecting. That has to be it, everything else looks correct.


this is the error i get with your changes

#1064 - 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 'exit("DELETE FROM `users` WHERE name = '$name'")' at line 1

i also tried running it with a name from the database in it instead of $name

Foster
11-20-2011, 04:43 PM
if it's that $name isnt correct then why would it work when i only run 1 query?

actually i get that error when i run it from the php and the below script works but only on 1 table


<?php

// connect to the database
include("config.php");

// connect to the mysql server
$link = mysql_connect($server, $db_user, $db_pass)
or die ("Could not connect to mysql because ".mysql_error());

// select the database
mysql_select_db($database)
or die ("Could not select database because ".mysql_error());

//Sanitize the $_POST name so its safe
$name = mysql_real_escape_string($_POST['name']);

// delete the entries
$ = "DELETE FROM `users` WHERE name = '$name'";

if(mysql_query($query))
{
echo "Query executed successfully.";
}


?>

kbluhm
11-20-2011, 04:44 PM
We're not getting anywhere here, and someone is actually offering to help you out hands-on with TeamViewer. Please please please let them do it. ;)

Another service we use at work constantly is join.me (http://join.me)... nothing to install.

tangoforce
11-20-2011, 05:55 PM
if it's that $name isnt correct then why would it work when i only run 1 query?

actually i get that error when i run it from the php and the below script works but only on 1 table

Well for a start you've screwed the code even more:
$ = "DELETE FROM `users` WHERE name = '$name'";

$ by itself doesn't do anything. It needs to be a named variable. The $ only tells PHP that its a variable. Without a name its useless.

Something like:
$Query = "DELETE FROM `users` WHERE name = '$name'";

I've offered to help you, not once have you even acknowledged it and then you continue to come back saying it won't work, you're giving up, you're gonna leave it etc. I don't understand why this is such an issue. I've offered, you've ignored, why keep posting about it? - YOU don't want it to work. If you did you'd be getting it fixed and working. Instead everytime someone gives you a bit of code you go and change it slightly to stop it working. Look at the code I gave you previously - I explicitly used $Name and then you went and changed them all to $name and complained that my code didn't work :rolleyes: (admittedly I'd missed out the word delete but you should of easily spotted that).

tangoforce
11-20-2011, 06:03 PM
Another service we use at work constantly is join.me (http://join.me)... nothing to install.

Thats a very good bit of kit there i like it (just tried it - very good). Think I'll offer that aswell as teamviewer from now on :thumbsup:

Foster
11-20-2011, 06:08 PM
i've not screwed the code, it was a typo, you know what they are dont you?

i changed you code from $Name to $name due to all of my other php and html refering it uses $name.

I've not said yes log in to my computer and change the code for 2 reasons, 1 i'd like to learn how to do this myself and 2 i'm not fan of letting someone i dont know have access to my computer with software i've never used before.

tangoforce
11-20-2011, 07:24 PM
i've not screwed the code, it was a typo, you know what they are dont you?

Not when copying and pasting no. Frankly I can't see how copying and pasting could drop off the variable name. You drag the cursor from the front of the $ to the end ; (if copying just one line).

IF you're typing everything here manually then its no wonder your code isn't working properly. Copy and paste and your / our code will be preserved.



i changed you code from $Name to $name due to all of my other php and html refering it uses $name.


Next time use the replace all option in your editor. It's there for that reason otherwise you're doing nothing to help yourself. You're then getting annoyed, saying silly things and making some of us frustrated too.

Replace all $Next with $name would of done the job.



I've not said yes log in to my computer and change the code for 2 reasons, 1 i'd like to learn how to do this myself and 2 i'm not fan of letting someone i dont know have access to my computer with software i've never used before.

Well thats your problem then. It doesn't allow someone to log in to your computer. It allows them to connect to it and see your desktop, use the mouse and keyboard just like you. It doesn't allow low level access or anything like that. Perhaps if you'd even looked at the teamviewer website and done some research then you'd be more enlightened about it and your problem could be fixed.

If you want to get somewhere in any programming language you've got to make the most of your resources, tools, contacts etc. While I can understand you wanting to fix your problem yourself, you've clearly reached the point where you're struggling to understand whats going on and that is why you've asked us for help. We've tried to help you to the max but you're not doing anything to help yourself here.

Have you even printed $name to the browser yet to be sure its the correct value?

tangoforce
11-21-2011, 03:40 PM
I know you are trying not to get your code working but I'm going to ask again:



Have you even printed $name to the browser yet to be sure its the correct value?

Foster
11-21-2011, 04:11 PM
actually i managed to get it working, it was a slightly modified version of adee's code

tangoforce
11-21-2011, 04:47 PM
Good!

So you didn't give up then? - See.. there really isn't any point moaning about giving up is there :thumbsup:



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum