These forums have been archived and are now read-only.

The new forums are live and can be found at https://forums.eveonline.com/

EVE Technology Lab

 
  • Topic is locked indefinitely.
 

Need some help with php
Author
Previous Topic Next Topic
shawn313
Cloaked Goof
Goonswarm Federation
#1 - 2013-07-11 00:49:30 UTC
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
Garoun Investment Bank
Gallente Federation
#2 - 2013-07-11 01:40:38 UTC
UPDATE tb1 SET row1 = row9
shawn313
Cloaked Goof
Goonswarm Federation
#3 - 2013-07-11 01:45:25 UTC
what if i need to add the cells and the update it?
Manhim
Garoun Investment Bank
Gallente Federation
#4 - 2013-07-11 01:52:28 UTC  |  Edited by: Manhim
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?
shawn313
Cloaked Goof
Goonswarm Federation
#5 - 2013-07-11 02:17:10 UTC
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
Garoun Investment Bank
Gallente Federation
#6 - 2013-07-11 02:40:34 UTC
UPDATE tb1, tb2
SET tb1.field1 = tb2.field9
WHERE tb1.field2 = tb2.field2;
shawn313
Cloaked Goof
Goonswarm Federation
#7 - 2013-07-11 07:07:02 UTC
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
Garoun Investment Bank
Gallente Federation
#8 - 2013-07-11 07:54:02 UTC  |  Edited by: Manhim
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 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
Vote Steve Ronuken for CSM
#9 - 2013-07-11 09:46:25 UTC
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/

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Manhim
Garoun Investment Bank
Gallente Federation
#10 - 2013-07-11 21:15:18 UTC
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
Cloaked Goof
Goonswarm Federation
#11 - 2013-07-11 21:57:48 UTC
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
Garoun Investment Bank
Gallente Federation
#12 - 2013-07-12 03:08:55 UTC