How do I Store Variables in a mySQL Table?

[survivorjoe]

I want to create a table that has a newsletter that will get emailed out to clients. In the table, I want a field for the Subject, and one for the Body. In the Subject line, I need to include a personalization variable, such as "$firstname". In the Body content, I need to store several personalization variables. How do I store the variable name when I insert the newsletters into the table? For example, the Subject line may be: "$firstname, Here is the info I want to send you". In the body, I may have something like:

"Presented to: $name"
"From: $my_name"
"$company_name"
"$company_phone"

etc...

When I load the newsletter, I just want to store the variable names, not any content. When I open the table to load the newsletter, I need to then substitute the variable names with the true names and other content.

How do I go about doing this?

- Joe

 

[r937]

i would store it in the database just like that --

"Presented to: $name"
"From: $my_name"
"$company_name"
"$company_phone"

then when you pull out a newsletter, do the substitution with php (or whatever your application language is)





r937.com | rudy.ca

 

[survivorjoe]

OK, but then when I run the following code, why does nothing get inserted into the table? When I take away the "$" from the subject and body, the data loads and gets retreived.

$name = "Joe Kamenar";
$email = "info@findandassign.com";
$phone = "215-480-2737";
$subject = "$firstname, This is a test message";
$body = "<b><font color = 'FF0000'>Hi $firstname. This is a test message.</font></b>";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query = "INSERT INTO client VALUES ('','$name','$email','$phone','$subject','$body')";
mysql_query($query);
mysql_close();

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM client";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

echo "<b><center>Database Output</center></b><br><br>";

$i=0;
while ($i < $num) {

$name=mysql_result($result,$i,"name");
$email=mysql_result($result,$i,"email");
$phone=mysql_result($result,$i,"phone");
$subject=mysql_result($result,$i,"subject");
$body=mysql_result($result,$i,"body");


echo "<b>Name: $name</b><br>Phone: $phone<br>Email: $email<br>Subject: $subject<br>Body: $body<br>";

$i++;
}

?>

 

[r937]

sorry, friend, i don't do php

this is the mysql forum

r937.com | rudy.ca

 

[rac2]

When you drop the dollar sign which line do you drop it from?
This one

$subject = "$firstname, This is a test message";

or this one

$query = "INSERT INTO client VALUES ('','$name','$email','$phone','$subject','$body')";

If it is this latter line then it may be an effect of the double quote around the string assigned to $query. In perl, "$subject" means a string with the value stored in $subject. Not sure what "'$subject'" means, possible the string enclosed in the single quotes, in other words literally $subject, which is always the same string.

You might try more explicit code for building the string by using the concatenation operator.

$query = "INSERT INTO client VALUES ('', '" .  $name . "', '" . $email . "', '" . $phone . "', '" . $subject . "', '" . $body . "')";

That is a little difficult to read. Use the dot operator, this means concatenate. The double quote marks are OK enclosing characters. The single quotes should be inside the double quotes, they are characters needed in the SQL statement.

You did not mention this but I would expect that you would have seen the string $subject on the web page output. I would think that is what was being stored.

Are you getting any error messages? Have you tried printing the resulting $query to see what is being built?

In any case this does not look like a SQL problem. I did php maintenance one time on an open source application and I only do maintenance on perl code. So you might take PHVs advice and post in the php forum.

 

[survivorjoe]

However, the problem seems to be in the mySQL, as the data does not load. Is there something I am doing wrong in the table insertion?

- Joe

 

[survivorjoe]

Here is the updated test code. Note I have taken out the $ from the subject and body lines:

--------------------
$name = "Joe Kamenar";
$email = "info@findandassign.com";
$phone = "215-480-2737";
$subject = "firstname, This is a test message";
$body = "<b><font color = 'FF0000'>Hi firstname. This is a test message.</font></b>";


mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query = "INSERT INTO client VALUES ('','$name','$email','$phone','$subject','$body')";
mysql_query($query) or die( mysql_error() );
mysql_close();


mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM client";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

echo "<b><center>Database Output</center></b><br><br>";

$i=0;
while ($i < $num) {

$name=mysql_result($result,$i,"name");
$email=mysql_result($result,$i,"email");
$phone=mysql_result($result,$i,"phone");
$subject=mysql_result($result,$i,"subject");
$body=mysql_result($result,$i,"body");


echo "<b>Name: $name</b><br>Phone: $phone<br>Email: $email<br>Subject: $subject<br>Body: $body<br>";

$i++;
}



?>
-----------------------------
Here is the error I get:

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 'FF0000'>Hi firstname. This is a test message.')' at line 1

- Joe

 

[survivorjoe]

Here is the solution:

$subject = mysql_escape_string("\$firstname, This is a test message");

$body = mysql_escape_string("<b><font color = 'FF0000'>Hi \$firstname. This is a test message.</font></b>");

This lets me store the variable name with the $ in front of it in the table.

Thanks for looking into this for me.

- Joe