Pages: [1] :: one page |
|
Author |
Thread Statistics | Show CCP posts - 0 post(s) |
shawn313
blacknights 313
0
|
Posted - 2013.07.11 00:49:00 -
[1] - Quote
so i have a datebase we will call it DB1 and i have 2 tables we will call them TB1 and TB2. i need to update 1 cell in TB1 with info form cell 9 in TB1 and i need to do this till every row has been updated |
Manhim
Cyan Ventures
2
|
Posted - 2013.07.11 01:40:00 -
[2] - Quote
UPDATE tb1 SET row1 = row9 |
shawn313
blacknights 313
0
|
Posted - 2013.07.11 01:45:00 -
[3] - Quote
what if i need to add the cells and the update it? |
Manhim
Cyan Ventures
2
|
Posted - 2013.07.11 01:52:00 -
[4] - Quote
You add the columns and then update it.
ALTER TABLE tb1 ADD row9 VARCHAR(60); UPDATE tb1 SET row1 = row9; |
shawn313
blacknights 313
0
|
Posted - 2013.07.11 02:17:00 -
[5] - Quote
Manhim wrote:You add the columns and then update it.
ALTER TABLE tb1 ADD row9 VARCHAR(60); UPDATE tb1 SET row1 = row9;
Or were you talking about rows? rows in 2 different tables |
Manhim
Cyan Ventures
2
|
Posted - 2013.07.11 02:40:00 -
[6] - Quote
UPDATE tb1, tb2 SET tb1.field1 = tb2.field9 WHERE tb1.field2 = tb2.field2; |
shawn313
blacknights 313
0
|
Posted - 2013.07.11 07:07:00 -
[7] - Quote
here is what i got cant put the php tags wont post if i do
Quote: include "connect.php"; include "settings.php"; $sql_query = "SELECT * FROM walletJournal"; $sql_res = mysql_query($sql_query); while ($row = mysql_fetch_array($sql_res)) { $date = ($row['date']); $refID = ($row['refID']); $ownerID1 = ($row['ownerID1']); $amount = ($row['amount']); $ownerName1 = ($row['ownerName1']); $processed = ($row['processed']); $done = ($row['done']); $sql_query2 = "SELECT * FROM members"; $sql_res2 = mysql_query($sql_query2); while ($row = mysql_fetch_array($sql_res)) { $username = ($row['username']); $isk = ($row['isk']); if ( $processed > 0 ) { mysql_query("UPDATE members, walletJournal SET $isk = $isk + $amount WHERE $username = $ownerID1;") or die(mysql_error()); mysql_query("UPDATE members SET done=1 WHERE refID=$refID") or die(mysql_error()); mysql_query("UPDATE walletJournal SET done=1 WHERE refID=$refID") or die(mysql_error()); mysql_query("UPDATE walletJournal SET processed=$sum WHERE refID=$refID") or die(mysql_error()); }
|
Manhim
Cyan Ventures
2
|
Posted - 2013.07.11 07:54:00 -
[8] - Quote
Ok, first think I noticed, mysql_connect and al are deprecated, you should avoid using this method to connect to a database as it will dissapear in the next PHP version. It is also slow and makes it too easy to create insecure queries (like yours).
You should start reading about PDO and prepared statements.
Your error: UPDATE members, walletJournal SET $isk = $isk + $amount WHERE $username = $ownerID1;
This is not good, you seem to be confusing the concept of variable and sql query. This is why I usually avoid using variables straight into a string. Here's a fix (not sure about the columns names, might want to double check):
mysql_query('UPDATE members, walletJournal SET member.isk = \'' . mysql_real_escape_string($isk + $amount) . '\' WHERE username = \' . mysql_real_escape_string($ownerID1) . \'') or die(mysql_error());
Using single quotes, you cannot but variable straight into the string like you can with double quotes. I've pretty much always choosen this code style to make sure that everyone I work with doesn't put variables into a string which can cause confusions. The mysql_real_escape_string function is there to secure your inputs and to "make sure" that it won't screw the query ("make sure" is in quotations because it still can be screwed-up, just harder to do, prepared statements basically protect you from all potential screw-ups [with some small exceptions that should be fixed by now]). |
Steve Ronuken
Fuzzwork Enterprises
1622
|
Posted - 2013.07.11 09:46:00 -
[9] - Quote
PDO is the way to go. It's a /trifle/ more complicated to use, but nothing significant.
define your SQL, using placeholders for any values you need to pass in (you can use named placeholders, or question marks) prepare the statement execute the statement, passing in any parameters needed to fill the placeholders
If you're running the same SQL multiple times, you can just hit execute multiple times, with different parameters.
An no need to worry about little bobby tables. http://xkcd.com/327/ Steve Ronuken for CSM 9!-á I'm starting early :) Handy tools and an SDE conversion Twitter: @fuzzysteve on Twitter |
Manhim
Cyan Ventures
4
|
Posted - 2013.07.11 21:15:00 -
[10] - Quote
Also, please note that the code I provided is merely a fix to what I saw and I'm not even sure if it does the right thing. I'd need more specific information in order to help. |
|
shawn313
blacknights 313
0
|
Posted - 2013.07.11 21:57:00 -
[11] - Quote
i am reading up on PDO if you have any places that are a good starting point for a noob like my self please point me two them thank you back to Google for now |
Manhim
Cyan Ventures
4
|
Posted - 2013.07.12 03:08:00 -
[12] - Quote
This is a good site: http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/ |
|
|
|
Pages: [1] :: one page |
First page | Previous page | Next page | Last page |