Using a variable in an UPDATE SQL query

[richiew13]

I am in the process of teaching myself PHP/SQL.

I have a seemingly simple thing I'm trying to do. I have a field called "memberid" in my database that is populated with a unique number for each row. I have another field called "member_id" that I want to populate with the same number that is in the "memberid" field for each row.

At this point, if there is an easier method to do it, doesn't really matter. I want to figure out why this method won't work for me.

I wrote a simple script to try and do this for me, but it just doesn't work.

$num=1;
while ($num <= 40) {
$query= "UPDATE members SET member_id='$num' WHERE memberid='$num'";
$num++;
}

 

[vacunita]

If this is the entire code, then you are only creating a string variable there and overwriting it in each iteration of your while loop.

So at the end all you would have is "UPDATE members SET member_id='40' WHERE memberid='40";






----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[richiew13]

Doesn't my code do the following:

1) set a variable ($num) equal to 1.
2) state that while the variable is less than 40, execute the following:
3) run the query based on $num being equal to 1.
4) set $num equal to 2.
5) return to step #2.

 

[jpadie]

the reason why it doesn't work is that you never run the query.

$num=1;
while ($num <= 40) {
 $query= "UPDATE members SET member_id='$num' WHERE memberid='$num'";
 [red]mysql_query($query);[/red]
  $num++;
}

it would be better to add some error checking in too.

and even better would be to use sql

mysql_query('Update members set member_id = memberid where memberid <=40');

 

[richiew13]

Thanks, jpadie.

I figured there was a better way to do this straight from sql. But I figured I could write a script to do it easily. I was wrong.

I am just now getting to learn how to use mysql with php. I've been reading books and tutorials slowly over the past couple of months. I can read books and do lessons, but I never REALLY learn how to do things like this until I have to get in there and think about it and troubleshoot on my own.

In my code above, i was kind of trying to copy some lessons I had done. Until now, I didn't understand how the query code works. Now I see that I set a variable ($query) which is full of the text that will be used for a query. Then, I execute the query with the "mysql_query" command. I thought $query was a command of itself. I was wrong.

Thanks for the help. You solved my problem!

 

[jpadie]

now worries.

some other things to bear in mind as you pick up sql and php skills:

1. never trust user generated data. always check that it is of a type that is permissible, and then escape it before using it in a query.

2. loops are an often used construct. there are a number of different loop types such as foreach; for; do ... while and while {}. all have their uses. typically i use a while {} loop for iterating over a database recordset, foreach loops for arrays and for loops for everything else. i can't remember having used a do ... while loop recently. I would have used a for loop for your script

for ($i=1; $i<=40; $i++){
  mysql_query("update members set member_id = $i where memberid=$i");
}

3. get your quoting style right, from the start. Personally I use heredoc for anything more than a couple of lines

4. get to grips with abstraction layers early on in your db coding. I started with the pear::db abstraction layer but now more or less exclusively use PDO (which is built into php). With PDO (and others), the coding takes care of a lot of the variable enquoting and escaping for you. leaving you only to check the permissibility of the relevant input. Personally I also mostly use a form abstraction layer as well (Pear::quickform_HTML)

 

[DonQuichote]

Ehmm, isn't this what you are trying to do?

UPDATE members SET member_id=memberid;

This will set the member_id equal to memberid for all rows in the table. If you really want to affect the whole table, this query does it.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[richiew13]

jpadie, thanks for the tips.

Don, thanks for that code as well. I was sure there was a better way then what I was doing. I just wanted to try and do it within the confines of my existing knowledge. And even then I couldn't do it. I came here because I knew my method was on the right track and SHOULD work.

That's one of the tough things about learning from a book. A book doesn't always explain every detail, and there's no way to ask the book a quick question.

 

[richiew13]

3. get your quoting style right, from the start. Personally I use heredoc for anything more than a couple of lines"

I researched heredoc here:

http://www.hudzilla.org/phpwiki/index.php?title=Heredoc_syntax

Is heredoc something that needs to be installed or defined? Or, can I just start using heredoc whenever I want, simply by using the <<< code?

 

[vacunita]

No heredoc is just a way of printing out text to the screen. Nothing needs to be installed. Just use the syntax and your there. Its helpful when you have lots of html, and you don't want to have to muck around with quotes and double quotes since its likely you'll have them both in the text.

<<<EOT
Text goes in here it can be ay type of text including html.
EOT;

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.