Categories > TinyButStrong general >

PHP Variables in MySQL Query

The forum is closed. Please use Stack Overflow for submitting new questions. Use tags: tinybutstrong , opentbs
By: Don Bledsoe
Date: 2007-05-08
Time: 13:56

PHP Variables in MySQL Query

I am having some difficulty figuring out the proper syntax to not get this error:
[TbsSql] Trace SQL: INSERT INTO domains (custLname,custFname,company,email,account,expiresdate,createdate,domain,hosted) VALUES (Doe,John,Big Web Site,john.doe@bigwebsite.com,1246,2008-03-01,2007-03-01,bigwebsite.com,F) LIMIT=1
[TbsSql] Database error message: 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 'Web Site,john.doe@bigwebsite.com,1246,2008-03-01,2007-03-01,big

The very basic PHP script is:
include_once('includes/tbs_class.php');
$TBS = new clsTinyButStrong;
include_once('includes/tbssql_mysql.php');
$Db = new clsTbsSQL('localhost', 'forefron_forefro', 'Zipp1ttY', 'forefron_domtrak');
$Db->Mode = 3;

if(isset($HTTP_POST_VARS['submit'])) {
    //    UPDATE record
    $lastname = addslashes($HTTP_POST_VARS['LastName']);
    $firstname = addslashes($HTTP_POST_VARS['FirstName']);
    $company = addslashes($HTTP_POST_VARS['Company']);
    $email = addslashes($HTTP_POST_VARS['Email']);
    $account = $HTTP_POST_VARS['Account'];
    $expires = $HTTP_POST_VARS['DomainExpires'];
    $created = $HTTP_POST_VARS['DomainCreated'];
    $domain = $HTTP_POST_VARS['DomainName'];
    $hosted = $HTTP_POST_VARS['Hosted'];
   
    $Db->Execute('INSERT INTO domains (custLname,custFname,company,email,account,expiresdate,createdate,domain,hosted) VALUES ('.$lastname.','.$firstname.','.$company.','.$email.','.$account.','.$expires.','.$created.','.$domain.','.$hosted.') LIMIT=1');
    $TBS->LoadTemplate('tpl/add.tpl.html');
    $TBS->Show();
}
else {
    $TBS->LoadTemplate('tpl/add.tpl.html');
    $TBS->Show();
}
I'm sure it's something insanely simple and I'm just missing the boat here. I am very new to TBS. so I am going through the initial learning curve and I'm not a professional programmer (that should be obvious). If someone could point me in the right direction, I'd be very grateful.

Don
By: Skrol29
Date: 2007-05-09
Time: 01:16

Re: PHP Variables in MySQL Query

Hi,

This is just because you did not put the string delimitors in your SQL statments.
... VALUES (\''.$lastname.'\',\''.$firstname.'\' ...

But this is not good neither because you also have to pretect your variables against string delimitors and SQL injection.

TbsSQL does it for you :
$Db->Execute('INSERT INTO domains (custLname,custFname,company,email,account,expiresdate,createdate,domain,hosted) VALUES (@1@,@2@,@3@,@4@,@5@,@6@,@7@,@8@,@9@)',$lastname,$firstname,$company,$email,$account,$expires,$created,$domain,$hosted);
By: Don Bledsoe
Date: 2007-05-09
Time: 02:41

Re: PHP Variables in MySQL Query

Ah, so THAT'S how that works! I get it now. I wish the documentation had more examples for newbies like me.

Thank you very much for the guidance.
By: Don Bledsoe
Date: 2007-05-26
Time: 03:03

Re: PHP Variables in MySQL Query

I'm sure this is very elementary, but I want to get MySQL data into PHP variables after a MergeBlock() call. For example, there's a field in my database called expiresdate and I want to get this date string into a PHP variable so I can call a function to calculate the number of days between today and the expiresdate. Can I simply use $expiresdate and make the call?
By: Skrol29
Date: 2007-05-26
Time: 13:03

Re: PHP Variables in MySQL Query

If you have to calculate the the number of days for several records, the you can use parameter "ondata" to call a function on each records.

But in your case, the more simple I think is to ask MySQL to calculate the number of days directly in the SQL statement.
By: Don Bledsoe
Date: 2007-05-26
Time: 15:43

Re: PHP Variables in MySQL Query

Here's where I get lost:

I need to do that calculation and MySQL SELECT statement is fine, but I need to in-effect create a field on-the-fly that contains the result. I'll call it expDays. Can I do that? I guess my problem is understanding where MySQL will put the result of the calculation for each record and is it available to me as a MergeBlock() or PHP variable?

An awkward way is to add an integer field to the database and run a query to update the days field and then run my SELECT statement. This would make the value available for the MergeBlock() as [blk.expDays]. If that's the case, I need an IF statement that is the same as this:
if(expDays < 31) {
    $cellbgcolor = "#FFB0B0";
} elseif(expDays < 61) {
    $cellbgcolor = "#FFFF66";
} elseif(expDays < 91) {
    $cellbgcolor = "#CCFFFF";
}
Can TBS handle this?
By: Skrol29
Date: 2007-05-26
Time: 17:46

Re: PHP Variables in MySQL Query

Ok, I understand.

You can do that with TBS, just take a look at the Example Page of this site, choose "event functions". The example is quite like your problem.