Mysql Insert Into

[liberate]

Hi Everyone.

 

There are a couple lines below, the INSERT INTO, that is likely "all kinds of bad". But that is as close as I can get it.

 

Could someone rewrite it. My knowlege of php and mysql is "see spot run".

 

Thanks in advance.

 

Tom

 

This is an addon to subscription mailing program. The program allows for a custom script to run after signup.php has completed. The form submision values are still available when this script runs. Connection to dbase still active.

 

 

<?php
// database connection already made

// going to need an if
if($list=='1'){  commands(); exit; }

$row = mysql_query("select uid from lm_users where list = 2 and email = '".addslashes($email)."';"); // Retrieves unique_id of the person just subscribed

list($uid)=mysql_fetch_row($row); // Since this only fetches one value/variable there is probably a better way to write it.

$row = mysql_query("select user2,user4,user5,user6,user10 from lm_users where list = 1 and user2 = '".addslashes($user2)."';"); //Retrieves other data

list($user2,$user4,$user5,$user6,$user10)=mysql_fetch_row($row);

// this is where I need help...
mysql_query("INSERT INTO lm_users (user2,user4,user5,user6,user10) where list = 2 and uid = $uid

VALUES ($user2,$user4,$user5,$user6,$user10)");

// Is this anything close to correct ???

?>

Edited February 15, 2013 by liberate

[liberate]

The "if" above should be "list==2" list_does_equal_2

The if statement just needs to continue with script if true or die if false

 

 

Having trouble posting to the forum, I'll try a different browser next time.

 

Any help would be appreciated

 

Thanks

Tom

[liberate]

Hi Everyone

 

OK so from my gooogling around, I found out that "Insert Into" and "where" don't belong together. Check this over and tell me if I am on the right track.

Thank you Tom

 

Additional_Script.php

 

<?php

// Signup.php has just created new mysql row with $list $uid $user2 $firstname $lastname $email. These values are still available when Additional_Script.php runs.

// database connection already made

if($list=='2'){

$row = mysql_query("select user2,user4,user5,user6,user10 from lm_users where list = 1 and user2 = $user2");

list($user2,$user4,$user5,$user6,$user10)=mysql_fetch_row($row); 

Update lm_users SET user2 = $user2, user4=$user4, user5=$user5, user6=$user6, user10=$user10 WHERE list = 2 and uid = $uid

}

?>

Edited February 15, 2013 by liberate

[fenway]

You know that SQL can tell you if your statement parses correctly -- and we can't guess -- but I can tell you that without quoting, you're going to in trouble. That's what placeholders are for.

[liberate]

Hi Guys

 

I have been banging my head against the wall for days trying to figure out the correct syntax. As I said in the first post, I do not code php or mysql. I have done my best to come as close as I can get. I can, if the code is simple enough, follow the logic of it. But to actually write code? No, that is beyond my knowledge and ability.

 

"You know that SQL can tell you if your statement parses correctly", I am sure it can but knowing how that is a different thing.

 

"Placeholders"? So I am off to google and php manual again, this time trying to figure out what placeholders are.

 

"and we can't guess" Now that one has me puzzled, You can't read what I am trying to do??? That's a hard one to believe.

 

I trying my best and have gone to the limit of my understanding of php and mysql.

 

I can understand being reluctant to provide the answers, to offer the pieces of the puzzle when the one who is asking has not tried and only wants someone else to do the work for them.

 

Best Regards guys

[fenway]

I simply meant that if you executed the statement, if would be plainly obvious from the syntax errors where the problems are.

[Jessica]

And no, we can't tell what you're trying to do. I can tell you if you have a table that has 10 fields called user1-user10, it's WAY wrong. 

 

Why don't you explain exactly what the steps are you are trying to complete?

[liberate]

Thank you Jessica

 

A quick peak into your "debuging your SQL" link i see:

 

$sql = "Your SQL statement here";

$result = mysqli_query($sql) or trigger_error("Query Failed! SQL: $sql - Error: ".mysqli_error(), E_USER_ERROR);

 

I assume that is what Fenway was referring to? I'll give it a try.

 

 

Fifteen years ago I created a self replicating website that over 2000 members used based off of www.site/?id=XXXX Where each member had their own id #. The code below took the id= from their personal url, retrieved from lm_user their firstname lastname email and their id# that was stored in user1.

 

I remember that the hardest part in working with the person who wrote this was attempting to get him to understand what I wanted to do because I couldn't tell him in the terms he understood.

 

If you can't read this then php code has changed dramatically in the last 15 years.

<?php

##function_default.php
function dbc() {

mysql_connect("localhost","User","Pass");
mysql_select_db("Databass");
}

$id	 = $_GET['id'];

dbc();

// SQL query find id=xxxx or default + addslashes prevents SQL injection (important!)
$row = mysql_query("select uid,fname,lname,email,user1,user2,user3 from lm_users where list = 1 and cnf = 1 and user1 = '".addslashes($id)."';");

// if no row returned then default
if(@mysql_num_rows($row)==0) $row = mysql_query("select uid,fname,lname,email,user1,user2,user3 from lm_users where list = 1 and user1 = 1247;");

list($uid,$fname,$lname,$email,$user1,$user2,$user3)=mysql_fetch_row($row);

$split = explode("@", $email);
 $exp_email = "<script language=\"Javascript\" type=\"text/javascript\">\n";
 $exp_email .= "<!--\n";
 $exp_email .= "var name = '".$split[0]."';\n";
 $exp_email .= "var domain = '".$split[1]."';\n";
 $exp_email .= "document.write(name + '@' + domain + '</a>');\n";
 $exp_email .= "// -->\n";
 $exp_email .= "</script>\n";

?>

 

In layman's terms this code retrieved the value of id= in www.site.com?id=XXXX . Using the idnumber retrieved it found the row in lm_user where list was 1 and id was the id from www.site.com?id=xxxx Having found the right row it retrieved the first (fname) last (lname) email and idnumber (user1) etc. and made them available as $uid $fname $lname $email $user1 $user2 and $user3.

 

If XXXX was missing or wrong and the SQL returned nothing, then the idnumber 1247 was used to retrieve $uid $fname $lname $email $user1 $user2 and $user3 so that the visitor wasn't staring at a blank website.

 

And the last lines were to make a little harder on the spambots to harvest everyone's email address.

 

Does this code make sense to you?

Edited February 17, 2013 by liberate

[liberate]

This is an explanation of the code I am trying to write. It is recycled from the code I used 15 years ago. Although the write to mysql is new.

 

$uid $list $fname $lname $email $user1 and $user2 are all known variables from the form submission that the main program (a purchased mailing program) just finished processing but are all still available to signup-xtra.php The purchased program allows for a custom script to be added ie. signup-xtra.php

 

signup-xtra.php

<?php

// The script only runs if list is equal to 2
if($list=='2'){
function dbc() {

mysql_connect("localhost","user","pass");
mysql_select_db("dbase");
}

dbc();


// All of the pieces of the statement are correct.
// If you find in lm_user where list is 1 and user2 is the variable $user2 you will have the right row.
// From that row I need that values stored in the columns user2, user4, user5, user6, and user10 

$row = mysql_query("select user2,user4,user5,user6,user10 from lm_users where list = 1 and user2 = $user2");



// I call this make_available_to for lack of a better way to say it.
// Make available to the script $user2, $user4, $user5, $user6, and $user10 the values found in $row above

list($user2,$user4,$user5,$user6,$user10)=mysql_fetch_row($row);



//Quoting from the top of this post: "$uid $list $fname $lname $email $user1 and $user2 are all known variables from the form submission that the main program has just finished processing but are all still available to signup-xtra.php" (this script)


//Locate the row in lm_users where list = 2 and uid = $uid (a known variable). There will only be one row.
//Update that row with the values retreived from $row above.
// Add to column "user4" the value contained in $user4	
// Add to column "user5" the value contained in $user5
// Add to column "user6" the value contained in $user6
// Add to column "user7" the value contained in $user2 You might assume that is an error but it is not
// Add to column "user10" the value contained in $user10
// These were retrieved from list 1 and are being written to list 2 so nothing is being overwritten

Update lm_users SET user4=$user4, user5=$user5, user6=$user6, user7=$user2, user10=$user10 WHERE list = 2 and uid = $uid;


// $to = "$user6"; // on pause
// $subject = "Form Submitted";
// $message = "$fname, $lname, $email, $user1";
// $from = "mail@mail.com";
// $headers = "From: $from";
// mail($to,$subject,$message,$headers);
}
?>

 

 

I hope this helps to explain what I am trying to do.

 

As always any help would be greatly appreciated.

 

Thank you

Tom

Edited February 17, 2013 by liberate

[Jessica]

We can read the code, that doesn't tell us what YOU WANT TO DO and most importantly WHY. As an aside, if PHP *HASN'T* changed dramatically in 15 years it would be dying. Have you bothered to do any reading about PHP???

 

 

1. Why do you have so many userX columns? normalize that data.

2. You don't need to select a row just to update another row. 

3. you have random SQL in your PHP code, not in a string, not being executed. 

4. you haven't even told us what is going wrong. We are not psychic. Only you know what you want to happen, and what you don't like about what does happen.

 

[liberate]

Thank you Jessica for the post.

 

1. Why do you have so many userX columns? normalize that data.

 

Listmailpro is a mailing program I have owned for 15 years. Listmailpro created the dbase during the install. There is no normalizing it. Besides the programs many lm_users fields uid(unique id) fname lname email etc. there are 10 user defined fields. Granted it would be nice to rename them, but Listmailpro refers to them by these userx names.

 

 

2. You don't need to select a row just to update another row.

 

No. But I need the data collected from one row, to update a different row

John in list 1 has stored his phone number in user1, his username in user2, his skype in user3 etc. and his email address in user6. Yes his email address is also in email, but he is allowed to use the same or different in user6. Only user8 and user9 are currently not being used.

 

Bill submitted the signup form for list 2 and Listmailpro processed the form about a nanosecond ago and then Listmailpro checked for existence of signup-xtra.php (a user added script) In my case signup-xtra.php needs to add some details to bill's (list2) record some of John's (list 1) stored information.

 

Namely:

John's name as wants it displayed held in user4

John's phone number if he chooses to add it to his user5

John's email, whichever one he want bill to know about, held in user6

etc. etc.

 

When the php mysql code of signup-xtra.php works it will let it will let John and anyone else in list 1 to use the same set of list 2 follow up letters each one customized with their name phone email etc. etc. to their respective Bills Susans Sharrons and Daves

 

"Where list = 1 and user2 = $user2" will be the correct row and from that row I need the values held in user2 4 5 6 and 10

 

"Where list =2 and uid = $uid" will be the right row, I need (user2 4 5 6 and 10 collected from "where list =1 and user = $user2") added to this row as user4=$user4, user5=$user5, user6=$user6, user7=$user2, user10=$user10

 

Don't question the logic, trust me this is exactly what needs to happen for this script to do it's job.

 

 

 

3. you have random SQL in your PHP code, not in a string, not being executed.

Ok. who? when? where? why? what? Until I know what you are referring to I can't fill in any of the blanks.

 

 

 

4. you haven't even told us what is going wrong. We are not psychic. Only you know what you want to happen, and what you don't like about what does happen.

 

I either see "page can not be displayed", or a blank page, or "echo of $uid $list $fname $lname $email $user1 and $user2 but never echo of $user2,$user4,$user5,$user6,$user10 which would prove retrieval from from the database. I removed the echo lines for posting as they were only for debugging.

 

 

 

I hope it helps

Tom

Edited February 17, 2013 by liberate

[Jessica]

The very last line that isn't a comment is an SQL statement, but it's not in a string. It's just sitting there in your code.

 

The blank page indicates you need to turn on error reporting.

[liberate]

Update:

 

Mysql select and mysql fetch row are now working. But the update is not working. Any hints on what I need to do?

 

 

<?php
// CUSTOM SCRIPT HERE

if($list=='2'){
$link = mysqli_connect(				 )

error_reporting(-1); // not that I have seen it do anything

$row = mysql_query("select user2,user4,user5,user6,user10 from lm_users where list = '1' and user2 =
'$user7'"); // working

list($user4,$user5,$user6,$user7,$user10)=mysql_fetch_row($row);	 // working

$sql = "Update lm_users SET user4='$user4', user5='$user5', user6='$user6', user7='$user2',
user10='$user10' WHERE list = '2' and uid = '$uid'";

echo "$user4<br>";
echo "$user5<br>";
echo "$user6<br>";
echo "$user7<br>";
echo "$user10<br>";

// $to = "$user6";
// $subject = "Form Submitted";
// $message = "$fname, $lname, $email, $user1";
// $from = "
// $headers = "From: $from";
// mail($to,$subject,$message,$headers);

mysqli_close($link);

}

?>

Edited February 18, 2013 by liberate

[Jessica]

You still never run the query. Now it's a string, which is better. But it's just a string.

[liberate]

Done. Thank you Jessica

 

Now is that all that different from what I posted before. No, it is almost verbatim. So did explaining the what and why, have any impact on the finished code, No. The way I see it, you and Fenway just didn't trust my logic.

 

All I was asking you or any goodhearted member was to help with what was wrong with the code.

 

Mind you I did learn a lot. And no, in day to day life I have very little need for php/mysql.

 

Best Regards

Tom

 

<?php

if($list=='2'){

$link = mysqli_connect("					 ");

$row = mysql_query("select user2,user4,user5,user6,user10 from lm_users where list = '1' and user2 = '$user2'");

list($user2,$user4,$user5,$user6,$user10)=mysql_fetch_row($row);

$sql = "Update lm_users SET user4='$user4', user5='$user5', user6='$user6', user7='$user2', user10='$user10' WHERE list = '2' and uid = '$uid'";
mysql_query($sql);

$to = "$user6";
$subject = "Form Submitted";
$message = "$fname, $lname, $email, $user1";
$from = "[email="foobar@mail.com"]foobar@mail.com[/email]";
$headers = "From: $from";
mail($to,$subject,$message,$headers);

mysqli_close($link);
}
?>

 

<?php
if($list=='2'){

$row = mysql_query("select user2,user4,user5,user6,user10 from lm_users where list = 1 and user2 = $user2");

list($user2,$user4,$user5,$user6,$user10)=mysql_fetch_row($row);

Update lm_users SET user2 = $user2, user4=$user4, user5=$user5, user6=$user6, user10=$user10 WHERE list = 2 and uid = $uid
}
?>

Edited February 18, 2013 by liberate

[Jessica]

And we should "trust" you why???

[fenway]

What we didn't "trust" was your explanation -- as you indicated, that's the hardest part to communicate.