Re: [PHP] SQL Syntax
On Tue, Jun 15, 2010 at 8:58 PM, Jan Reiter the-fal...@gmx.net wrote: Hi folks! I'm kind of ashamed to ask a question, as I haven't followed this list very much lately. This isn't exactly a PHP question, but since mysql is the most popular database engine used with php, I figured someone here might have an idea. I have 2 tables. Table A containing 2 fields. A user ID and a picture ID = A(uid,pid) and another table B, containing 3 fields. The picture ID, an attribute ID and a value for that attribute = B(pid,aid,value). Table B contains several rows for a single PID with various AIDs and values. Each AID is unique to a PID. (e.g. AID = 1 always holding the value for the image size and AID = 3 always holding a value for the image type) This is known as an EAV (Entity-Attribute-Value) design. It is usually (some would say always) a very bad idea to implement this in a relational database. and this is no exception. The goal is now to join table A on table B using pid, and selecting the rows based on MULTIPLE attributes. So the result should only contain rows for images, that relate to an attribute ID = 1 (size) that is bigger than 100 AND!!! an attribute ID = 5 that equals 'jpg'. I know that there is an easy solution to this, doing it in one query and I have the feeling, that I can almost touch it with my fingertips in my mind, but I can't go that final step, if you know what I mean. AND THAT DRIVES ME CRAZY!! The easy solution is to redesign the tables. There are a lot of reasons why this design is usually a very bad idea. For starters, what should be a simple query is anything but simple, as you have just discovered. What's more, there is no simple way (if any way at all) for your design to prevent an image from having a mime-type of 20174 or a size of 'jpg'. Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Syntax [improved SQL]
Hi, this is the solution I came up with, that is over 10 times faster than my first attemps. Tested @31,871 entries in table 'picture' and 222,712 entries in table 'picture_attrib_rel'. Old Version: SELECT * FROM picture as p INNER JOIN picture_attrib_rel as pr1 ON (p.pid = pr1.pid) INNER JOIN picture_attrib_rel as pr2 ON (p.pid = pr2.pid and pr2.val_int 1500) WHERE pr1.aid = 2 AND pr1.val_int = 1500 AND pr2.aid = 5 AND pr2.val_int 1000 Takes about 1.9 Seconds on average to return. The version with temporary tables: DROP temporary table if exists tmp_size; DROP temporary table if exists tmp_qi; CREATE temporary table tmp_size SELECT pid FROM picture_attrib_rel WHERE aid = 2 AND val_int = 1500; CREATE temporary table tmp_qi SELECT pid FROM picture_attrib_rel WHERE aid = 5 AND val_int 1000; SELECT pid,uid FROM tmp_size JOIN tmp_qi USING(pid) JOIN pictures USING(pid); DROP temporary table if exists tmp_size; DROP temporary table if exists tmp_qi; This takes 0.12 seconds to return, which is quite bearable for now. Thanks again for all your input! Regards, Jan -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] SQL Syntax [improved SQL]
-Original Message- From: Jan Reiter [mailto:the-fal...@gmx.net] Sent: Wednesday, June 16, 2010 8:55 AM To: php-general@lists.php.net Subject: Re: [PHP] SQL Syntax [improved SQL] Hi, this is the solution I came up with, that is over 10 times faster than my first attemps. Tested @31,871 entries in table 'picture' and 222,712 entries in table 'picture_attrib_rel'. Old Version: SELECT * FROM picture as p INNER JOIN picture_attrib_rel as pr1 ON (p.pid = pr1.pid) INNER JOIN picture_attrib_rel as pr2 ON (p.pid = pr2.pid and pr2.val_int 1500) WHERE pr1.aid = 2 AND pr1.val_int = 1500 AND pr2.aid = 5 AND pr2.val_int 1000 Takes about 1.9 Seconds on average to return. The version with temporary tables: DROP temporary table if exists tmp_size; DROP temporary table if exists tmp_qi; CREATE temporary table tmp_size SELECT pid FROM picture_attrib_rel WHERE aid = 2 AND val_int = 1500; CREATE temporary table tmp_qi SELECT pid FROM picture_attrib_rel WHERE aid = 5 AND val_int 1000; SELECT pid,uid FROM tmp_size JOIN tmp_qi USING(pid) JOIN pictures USING(pid); DROP temporary table if exists tmp_size; DROP temporary table if exists tmp_qi; This takes 0.12 seconds to return, which is quite bearable for now. Thanks again for all your input! Regards, Jan Jan, What do you get from this query and how fast does it execute? SELECT * FROM picture_attrib_rel par INNER JOIN pictures p ON p.pid = par.pid WHERE (par.aid = 2 AND par.val_int = 1500) OR (par.aid = 5 AND par.val_int 1000) Regards, Tommy -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Syntax
[Top-post.] You'll probably have much better luck on the MySQL General list. CC'ed on this email. On Tue, Jun 15, 2010 at 20:58, Jan Reiter the-fal...@gmx.net wrote: Hi folks! I'm kind of ashamed to ask a question, as I haven't followed this list very much lately. This isn't exactly a PHP question, but since mysql is the most popular database engine used with php, I figured someone here might have an idea. I have 2 tables. Table A containing 2 fields. A user ID and a picture ID = A(uid,pid) and another table B, containing 3 fields. The picture ID, an attribute ID and a value for that attribute = B(pid,aid,value). Table B contains several rows for a single PID with various AIDs and values. Each AID is unique to a PID. (e.g. AID = 1 always holding the value for the image size and AID = 3 always holding a value for the image type) The goal is now to join table A on table B using pid, and selecting the rows based on MULTIPLE attributes. So the result should only contain rows for images, that relate to an attribute ID = 1 (size) that is bigger than 100 AND!!! an attribute ID = 5 that equals 'jpg'. I know that there is an easy solution to this, doing it in one query and I have the feeling, that I can almost touch it with my fingertips in my mind, but I can't go that final step, if you know what I mean. AND THAT DRIVES ME CRAZY!! I appreciate your thoughts on this. Regards, Jan -- /Daniel P. Brown daniel.br...@parasane.net || danbr...@php.net http://www.parasane.net/ || http://www.pilotpig.net/ We now offer SAME-DAY SETUP on a new line of servers! -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Syntax
On Wed, 2010-06-16 at 02:58 +0200, Jan Reiter wrote: Hi folks! I'm kind of ashamed to ask a question, as I haven't followed this list very much lately. This isn't exactly a PHP question, but since mysql is the most popular database engine used with php, I figured someone here might have an idea. I have 2 tables. Table A containing 2 fields. A user ID and a picture ID = A(uid,pid) and another table B, containing 3 fields. The picture ID, an attribute ID and a value for that attribute = B(pid,aid,value). Table B contains several rows for a single PID with various AIDs and values. Each AID is unique to a PID. (e.g. AID = 1 always holding the value for the image size and AID = 3 always holding a value for the image type) The goal is now to join table A on table B using pid, and selecting the rows based on MULTIPLE attributes. So the result should only contain rows for images, that relate to an attribute ID = 1 (size) that is bigger than 100 AND!!! an attribute ID = 5 that equals 'jpg'. I know that there is an easy solution to this, doing it in one query and I have the feeling, that I can almost touch it with my fingertips in my mind, but I can't go that final step, if you know what I mean. AND THAT DRIVES ME CRAZY!! I appreciate your thoughts on this. Regards, Jan You'll be looking for something like this (untested): SELECT * FROM a LEFT JOIN b ON (a.pid = b.pid) WHERE (b.aid = 1 AND b.value 100) OR (b.aid = 3 AND b.value = 'jpg') Obviously instead of the * you may have to change to a list of field names to avoid fieldname collision on the two tables. Thanks, Ash http://www.ashleysheridan.co.uk
RE: [PHP] SQL Syntax
Thanks. That was my first attempt, too. Only this will throw out rows, that meet only one of the conditions, too. For example, I would get all pictures that are bigger than 100, regardless of type, and all pictures that are of type jpg, no matter the size. Doing it with a view would be an option, but that would immensely decrease flexibility. I guess I have to keep on cooking my brain on this ;-) I think I did it before, a few years ago when MySQL didn't support views yet, but I can't find that stuff ... @Dan: Thanks for forwarding my mail to the MySQL List! Regards, Jan From: Ashley Sheridan [mailto:a...@ashleysheridan.co.uk] Sent: Wednesday, June 16, 2010 3:09 AM To: Jan Reiter Cc: php-general@lists.php.net Subject: Re: [PHP] SQL Syntax On Wed, 2010-06-16 at 02:58 +0200, Jan Reiter wrote: Hi folks! I'm kind of ashamed to ask a question, as I haven't followed this list very much lately. This isn't exactly a PHP question, but since mysql is the most popular database engine used with php, I figured someone here might have an idea. I have 2 tables. Table A containing 2 fields. A user ID and a picture ID = A(uid,pid) and another table B, containing 3 fields. The picture ID, an attribute ID and a value for that attribute = B(pid,aid,value). Table B contains several rows for a single PID with various AIDs and values. Each AID is unique to a PID. (e.g. AID = 1 always holding the value for the image size and AID = 3 always holding a value for the image type) The goal is now to join table A on table B using pid, and selecting the rows based on MULTIPLE attributes. So the result should only contain rows for images, that relate to an attribute ID = 1 (size) that is bigger than 100 AND!!! an attribute ID = 5 that equals 'jpg'. I know that there is an easy solution to this, doing it in one query and I have the feeling, that I can almost touch it with my fingertips in my mind, but I can't go that final step, if you know what I mean. AND THAT DRIVES ME CRAZY!! I appreciate your thoughts on this. Regards, Jan You'll be looking for something like this (untested): SELECT * FROM a LEFT JOIN b ON (a.pid = b.pid) WHERE (b.aid = 1 AND b.value 100) OR (b.aid = 3 AND b.value = 'jpg') Obviously instead of the * you may have to change to a list of field names to avoid fieldname collision on the two tables. Thanks, Ash http://www.ashleysheridan.co.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] SQL Syntax
On Wed, 2010-06-16 at 03:23 +0200, Jan Reiter wrote: Thanks. That was my first attempt, too. Only this will throw out rows, that meet only one of the conditions, too. For example, I would get all pictures that are bigger than 100, regardless of type, and all pictures that are of type jpg, no matter the size. Doing it with a view would be an option, but that would immensely decrease flexibility. I guess I have to keep on cooking my brain on this ;-) I think I did it before, a few years ago when MySQL didn't support views yet, but I can't find that stuff ... @Dan: Thanks for forwarding my mail to the MySQL List! Regards, Jan From: Ashley Sheridan [mailto:a...@ashleysheridan.co.uk] Sent: Wednesday, June 16, 2010 3:09 AM To: Jan Reiter Cc: php-general@lists.php.net Subject: Re: [PHP] SQL Syntax On Wed, 2010-06-16 at 02:58 +0200, Jan Reiter wrote: Hi folks! I'm kind of ashamed to ask a question, as I haven't followed this list very much lately. This isn't exactly a PHP question, but since mysql is the most popular database engine used with php, I figured someone here might have an idea. I have 2 tables. Table A containing 2 fields. A user ID and a picture ID = A(uid,pid) and another table B, containing 3 fields. The picture ID, an attribute ID and a value for that attribute = B(pid,aid,value). Table B contains several rows for a single PID with various AIDs and values. Each AID is unique to a PID. (e.g. AID = 1 always holding the value for the image size and AID = 3 always holding a value for the image type) The goal is now to join table A on table B using pid, and selecting the rows based on MULTIPLE attributes. So the result should only contain rows for images, that relate to an attribute ID = 1 (size) that is bigger than 100 AND!!! an attribute ID = 5 that equals 'jpg'. I know that there is an easy solution to this, doing it in one query and I have the feeling, that I can almost touch it with my fingertips in my mind, but I can't go that final step, if you know what I mean. AND THAT DRIVES ME CRAZY!! I appreciate your thoughts on this. Regards, Jan You'll be looking for something like this (untested): SELECT * FROM a LEFT JOIN b ON (a.pid = b.pid) WHERE (b.aid = 1 AND b.value 100) OR (b.aid = 3 AND b.value = 'jpg') Obviously instead of the * you may have to change to a list of field names to avoid fieldname collision on the two tables. Thanks, Ash http://www.ashleysheridan.co.uk I think maybe your table structure could do with a little work, as it doesn't lend itself to simple queries. It could probably be done the way you need with sub-queries, but as the tables become more populated and more people are triggering the queries, this is going to become very slow. Thanks, Ash http://www.ashleysheridan.co.uk
[PHP] SQL Syntax
Hi folks! I'm kind of ashamed to ask a question, as I haven't followed this list very much lately. This isn't exactly a PHP question, but since mysql is the most popular database engine used with php, I figured someone here might have an idea. I have 2 tables. Table A containing 2 fields. A user ID and a picture ID = A(uid,pid) and another table B, containing 3 fields. The picture ID, an attribute ID and a value for that attribute = B(pid,aid,value). Table B contains several rows for a single PID with various AIDs and values. Each AID is unique to a PID. (e.g. AID = 1 always holding the value for the image size and AID = 3 always holding a value for the image type) The goal is now to join table A on table B using pid, and selecting the rows based on MULTIPLE attributes. So the result should only contain rows for images, that relate to an attribute ID = 1 (size) that is bigger than 100 AND!!! an attribute ID = 5 that equals 'jpg'. I know that there is an easy solution to this, doing it in one query and I have the feeling, that I can almost touch it with my fingertips in my mind, but I can't go that final step, if you know what I mean. AND THAT DRIVES ME CRAZY!! I appreciate your thoughts on this. Regards, Jan
[PHP] SQL syntax?
Hi I am having problems (yep me again) with my sql, I have looked and tried
different things (ASC, DESC, etc) but it same error:
Here is the error:
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 'ORDER BY
StartDate DESC' at line 2 --and the actual line the code is on
is line 21 not 2 so that is weird...and I had a comma between DESC and the
field but nothing
Code:
$sql = SELECT WorkOrderID AS Work_Order_ID, DATE_FORMAT(StartDate, '%b.
%e, %Y %l:%i %p') AS Start_Date,
DATE_FORMAT(EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ;
$sql .= Advertiser AS Advertiser_Name,AccountNum AS Account_Number,
Impressions AS Ad_Impressions, ;
$sql .= AdSize AS Ad_Size, CPM AS CPM_Rate, ;
$sql .= ORDER BY StartDate DESC;
could it be the AS, I copied that from another code--- I am trying to
make a report from the tableHere is my full script:
?php
include(../inc/dbconn_open.php);
if (empty($_SESSION['AdminLogin']) OR $_SESSION['AdminLogin'] 'OK' ){
header (Location: LogOut.php);
}
$query = SELECT WorkOrderID, Advertiser, AccountNum, Impressions,
AdSize, StartDate, EndDate, CPM, OnlineDate FROM workorderform;
$result = mysql_query ($query) or die(mysql_error());
$row = mysql_fetch_object ($result);
if ($row-UserReport == NO) {
header (Location: Welcome.php?AdminID=$AdminIDmsg=Sorry, you do
not have access to that page.);
}
$sql = SELECT WorkOrderID AS Work_Order_ID, DATE_FORMAT(StartDate, '%b.
%e, %Y %l:%i %p') AS Start_Date,
DATE_FORMAT(EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ;
$sql .= Advertiser AS Advertiser_Name,AccountNum AS Account_Number,
Impressions AS Ad_Impressions, ;
$sql .= AdSize AS Ad_Size, CPM AS CPM_Rate, ;
$sql .= ORDER BY StartDate DESC;
$export = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_object ($result);
$fields = mysql_num_fields($export);
$header = ;
$value = ;
$data = ;
for ($i = 0; $i $fields; $i++) {
$header .= mysql_field_name($export, $i) . \t;
}
while($row2 = mysql_fetch_row($export)) {
$line = '';
foreach($row2 as $value)
{
if ((!isset($value)) OR ($value == )) {
$value = \t;
} else {
$value = str_replace('', '', $value);
$value = '' . $value . '' . \t;
}
$line .= $value;
}
$data .= trim($line).\n;
}
$data = str_replace(\r,,$data);
if ($data == ) {
$data = \n(0) Records Found!\n;
}
header(Content-type: application/x-msdownload);
header(Content-Disposition: attachment; filename=AdDates_Report.xls);
header(Pragma: no-cache);
header(Expires: 0);
print $header\n$data;
?
Re: [PHP] SQL syntax?
$sql = SELECT WorkOrderID AS Work_Order_ID, DATE_FORMAT(StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= Advertiser AS Advertiser_Name,AccountNum AS Account_Number, Impressions AS Ad_Impressions, ; $sql .= AdSize AS Ad_Size, CPM AS CPM_Rate, ; $sql .= ORDER BY StartDate DESC; You forgot the tables, plus you have an extra comma after CPM_Rate. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL syntax?
On Fri, Dec 5, 2008 at 3:57 PM, Allan Arguelles [EMAIL PROTECTED]wrote: $sql = SELECT WorkOrderID AS Work_Order_ID, DATE_FORMAT(StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= Advertiser AS Advertiser_Name,AccountNum AS Account_Number, Impressions AS Ad_Impressions, ; $sql .= AdSize AS Ad_Size, CPM AS CPM_Rate, ; $sql .= ORDER BY StartDate DESC; You forgot the tables, plus you have an extra comma after CPM_Rate. well I changed it to: $sql = SELECT workorderform.WorkOrderID AS Work_Order_ID, DATE_FORMAT(workorderform.StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(workorderform.EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= workorderform.Advertiser AS Advertiser_Name,workorderform.AccountNum AS Account_Number, workorderform.Impressions AS Ad_Impressions, ; $sql .= workorderform.AdSize AS Ad_Size, workorderform.CPM AS CPM_Rate ; $sql .= ORDER BY StartDate DESC; and got the same error
Re: [PHP] SQL syntax?
Umm.. I meant you need to put $sql .= FROM workorderform ; between these: $sql .= AdSize AS Ad_Size, CPM AS CPM_Rate, ; $sql .= ORDER BY StartDate DESC; :) Terion Miller wrote: On Fri, Dec 5, 2008 at 3:57 PM, Allan Arguelles [EMAIL PROTECTED]wrote: $sql = SELECT WorkOrderID AS Work_Order_ID, DATE_FORMAT(StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= Advertiser AS Advertiser_Name,AccountNum AS Account_Number, Impressions AS Ad_Impressions, ; $sql .= AdSize AS Ad_Size, CPM AS CPM_Rate, ; $sql .= ORDER BY StartDate DESC; You forgot the tables, plus you have an extra comma after CPM_Rate. well I changed it to: $sql = SELECT workorderform.WorkOrderID AS Work_Order_ID, DATE_FORMAT(workorderform.StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(workorderform.EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= workorderform.Advertiser AS Advertiser_Name,workorderform.AccountNum AS Account_Number, workorderform.Impressions AS Ad_Impressions, ; $sql .= workorderform.AdSize AS Ad_Size, workorderform.CPM AS CPM_Rate ; $sql .= ORDER BY StartDate DESC; and got the same error
Re: [PHP] SQL syntax?
ah...I also though it was because I didn't have a statement like where adsize = adsize or something but I tried that and got the same error I have been getting ... 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 'FROM workorderform, WHERE WorkOrderID = WorkOrderID ORDER BY StartDate DESC' at line 2 and why does it keep saying line 2... here is the snippet as it is now: $sql = SELECT WorkOrderID AS Work_Order_ID, DATE_FORMAT(StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= Advertiser AS Advertiser_Name,AccountNum AS Account_Number, Impressions AS Ad_Impressions, ; $sql .= AdSize AS Ad_Size, CPM AS CPM_Rate, ; $sql.= FROM workorderform, ; $sql .= WHERE WorkOrderID = WorkOrderID ORDER BY StartDate DESC; On Fri, Dec 5, 2008 at 4:14 PM, Allan Arguelles [EMAIL PROTECTED]wrote: Umm.. I meant you need to put $sql .= FROM workorderform ; between these: $sql .= AdSize AS Ad_Size, CPM AS CPM_Rate, ; $sql .= ORDER BY StartDate DESC; :) Terion Miller wrote: On Fri, Dec 5, 2008 at 3:57 PM, Allan Arguelles [EMAIL PROTECTED] [EMAIL PROTECTED]wrote: $sql = SELECT WorkOrderID AS Work_Order_ID, DATE_FORMAT(StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= Advertiser AS Advertiser_Name,AccountNum AS Account_Number, Impressions AS Ad_Impressions, ; $sql .= AdSize AS Ad_Size, CPM AS CPM_Rate, ; $sql .= ORDER BY StartDate DESC; You forgot the tables, plus you have an extra comma after CPM_Rate. well I changed it to: $sql = SELECT workorderform.WorkOrderID AS Work_Order_ID, DATE_FORMAT(workorderform.StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(workorderform.EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= workorderform.Advertiser AS Advertiser_Name,workorderform.AccountNum AS Account_Number, workorderform.Impressions AS Ad_Impressions, ; $sql .= workorderform.AdSize AS Ad_Size, workorderform.CPM AS CPM_Rate ; $sql .= ORDER BY StartDate DESC; and got the same error
Re: [PHP] SQL syntax?
Try this: $sql = SELECT WorkOrderID AS Work_Order_ID, DATE_FORMAT(StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= Advertiser AS Advertiser_Name,AccountNum AS Account_Number, Impressions AS Ad_Impressions, ; $sql .= AdSize AS Ad_Size, CPM AS CPM_Rate ; $sql.= FROM workorderform ; $sql .= WHERE WorkOrderID = WorkOrderID ORDER BY StartDate DESC; I just removed extra commas from CPM_Rate and workorderform Terion Miller wrote: ah...I also though it was because I didn't have a statement like where adsize = adsize or something but I tried that and got the same error I have been getting ... 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 'FROM workorderform, WHERE WorkOrderID = WorkOrderID ORDER BY StartDate DESC' at line 2 and why does it keep saying line 2... here is the snippet as it is now: $sql = SELECT WorkOrderID AS Work_Order_ID, DATE_FORMAT(StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= Advertiser AS Advertiser_Name,AccountNum AS Account_Number, Impressions AS Ad_Impressions, ; $sql .= AdSize AS Ad_Size, CPM AS CPM_Rate, ; $sql.= FROM workorderform, ; $sql .= WHERE WorkOrderID = WorkOrderID ORDER BY StartDate DESC; On Fri, Dec 5, 2008 at 4:14 PM, Allan Arguelles [EMAIL PROTECTED]wrote: Umm.. I meant you need to put $sql .= FROM workorderform ; between these: $sql .= AdSize AS Ad_Size, CPM AS CPM_Rate, ; $sql .= ORDER BY StartDate DESC; :) Terion Miller wrote: On Fri, Dec 5, 2008 at 3:57 PM, Allan Arguelles [EMAIL PROTECTED] [EMAIL PROTECTED]wrote: $sql = SELECT WorkOrderID AS Work_Order_ID, DATE_FORMAT(StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= Advertiser AS Advertiser_Name,AccountNum AS Account_Number, Impressions AS Ad_Impressions, ; $sql .= AdSize AS Ad_Size, CPM AS CPM_Rate, ; $sql .= ORDER BY StartDate DESC; You forgot the tables, plus you have an extra comma after CPM_Rate. well I changed it to: $sql = SELECT workorderform.WorkOrderID AS Work_Order_ID, DATE_FORMAT(workorderform.StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(workorderform.EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= workorderform.Advertiser AS Advertiser_Name,workorderform.AccountNum AS Account_Number, workorderform.Impressions AS Ad_Impressions, ; $sql .= workorderform.AdSize AS Ad_Size, workorderform.CPM AS CPM_Rate ; $sql .= ORDER BY StartDate DESC; and got the same error
Re: [PHP] SQL syntax?
Excellent Allan thanks so much, sometimes I think php is causing me blindness!! Terion On Fri, Dec 5, 2008 at 4:26 PM, Allan Arguelles [EMAIL PROTECTED]wrote: Try this: $sql = SELECT WorkOrderID AS Work_Order_ID, DATE_FORMAT(StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= Advertiser AS Advertiser_Name,AccountNum AS Account_Number, Impressions AS Ad_Impressions, ; $sql .= AdSize AS Ad_Size, CPM AS CPM_Rate ; $sql.= FROM workorderform ; $sql .= WHERE WorkOrderID = WorkOrderID ORDER BY StartDate DESC; I just removed extra commas from CPM_Rate and workorderform Terion Miller wrote: ah...I also though it was because I didn't have a statement like where adsize = adsize or something but I tried that and got the same error I have been getting ... 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 'FROM workorderform, WHERE WorkOrderID = WorkOrderID ORDER BY StartDate DESC' at line 2 and why does it keep saying line 2... here is the snippet as it is now: $sql = SELECT WorkOrderID AS Work_Order_ID, DATE_FORMAT(StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= Advertiser AS Advertiser_Name,AccountNum AS Account_Number, Impressions AS Ad_Impressions, ; $sql .= AdSize AS Ad_Size, CPM AS CPM_Rate, ; $sql.= FROM workorderform, ; $sql .= WHERE WorkOrderID = WorkOrderID ORDER BY StartDate DESC; On Fri, Dec 5, 2008 at 4:14 PM, Allan Arguelles [EMAIL PROTECTED] [EMAIL PROTECTED]wrote: Umm.. I meant you need to put $sql .= FROM workorderform ; between these: $sql .= AdSize AS Ad_Size, CPM AS CPM_Rate, ; $sql .= ORDER BY StartDate DESC; :) Terion Miller wrote: On Fri, Dec 5, 2008 at 3:57 PM, Allan Arguelles [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED]wrote: $sql = SELECT WorkOrderID AS Work_Order_ID, DATE_FORMAT(StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= Advertiser AS Advertiser_Name,AccountNum AS Account_Number, Impressions AS Ad_Impressions, ; $sql .= AdSize AS Ad_Size, CPM AS CPM_Rate, ; $sql .= ORDER BY StartDate DESC; You forgot the tables, plus you have an extra comma after CPM_Rate. well I changed it to: $sql = SELECT workorderform.WorkOrderID AS Work_Order_ID, DATE_FORMAT(workorderform.StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(workorderform.EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= workorderform.Advertiser AS Advertiser_Name,workorderform.AccountNum AS Account_Number, workorderform.Impressions AS Ad_Impressions, ; $sql .= workorderform.AdSize AS Ad_Size, workorderform.CPM AS CPM_Rate ; $sql .= ORDER BY StartDate DESC; and got the same error
Re: [PHP] SQL syntax?
On Fri, 2008-12-05 at 16:51 -0600, Terion Miller wrote: Excellent Allan thanks so much, sometimes I think php is causing me blindness!! Terion On Fri, Dec 5, 2008 at 4:26 PM, Allan Arguelles [EMAIL PROTECTED]wrote: Try this: $sql = SELECT WorkOrderID AS Work_Order_ID, DATE_FORMAT(StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= Advertiser AS Advertiser_Name,AccountNum AS Account_Number, Impressions AS Ad_Impressions, ; $sql .= AdSize AS Ad_Size, CPM AS CPM_Rate ; $sql.= FROM workorderform ; $sql .= WHERE WorkOrderID = WorkOrderID ORDER BY StartDate DESC; I just removed extra commas from CPM_Rate and workorderform Terion Miller wrote: ah...I also though it was because I didn't have a statement like where adsize = adsize or something but I tried that and got the same error I have been getting ... 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 'FROM workorderform, WHERE WorkOrderID = WorkOrderID ORDER BY StartDate DESC' at line 2 and why does it keep saying line 2... here is the snippet as it is now: $sql = SELECT WorkOrderID AS Work_Order_ID, DATE_FORMAT(StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= Advertiser AS Advertiser_Name,AccountNum AS Account_Number, Impressions AS Ad_Impressions, ; $sql .= AdSize AS Ad_Size, CPM AS CPM_Rate, ; $sql.= FROM workorderform, ; $sql .= WHERE WorkOrderID = WorkOrderID ORDER BY StartDate DESC; On Fri, Dec 5, 2008 at 4:14 PM, Allan Arguelles [EMAIL PROTECTED] [EMAIL PROTECTED]wrote: Umm.. I meant you need to put $sql .= FROM workorderform ; between these: $sql .= AdSize AS Ad_Size, CPM AS CPM_Rate, ; $sql .= ORDER BY StartDate DESC; :) Terion Miller wrote: On Fri, Dec 5, 2008 at 3:57 PM, Allan Arguelles [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED]wrote: $sql = SELECT WorkOrderID AS Work_Order_ID, DATE_FORMAT(StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= Advertiser AS Advertiser_Name,AccountNum AS Account_Number, Impressions AS Ad_Impressions, ; $sql .= AdSize AS Ad_Size, CPM AS CPM_Rate, ; $sql .= ORDER BY StartDate DESC; You forgot the tables, plus you have an extra comma after CPM_Rate. well I changed it to: $sql = SELECT workorderform.WorkOrderID AS Work_Order_ID, DATE_FORMAT(workorderform.StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(workorderform.EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= workorderform.Advertiser AS Advertiser_Name,workorderform.AccountNum AS Account_Number, workorderform.Impressions AS Ad_Impressions, ; $sql .= workorderform.AdSize AS Ad_Size, workorderform.CPM AS CPM_Rate ; $sql .= ORDER BY StartDate DESC; and got the same error If I run into troubles with SQL (specifically MySQL) I run the query in phpMyAdmin, which is so helpful. If you're using another SQL variant like that god-forsaken M$ SQL, then you have to use the appropriate tool to interface with the database there. It sure helps remove the SQL problems from PHP, which was your problem in this case. Ash www.ashleysheridan.co.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] sql syntax using sprintf
until i started using the techniques for avoiding sql injection, i have been
using a normal insert and select sql query which worked fine.
i have a registration page where a user enters their username and if this
already exists i display a message by executing a select query and if the
username does not exist then i run an insert query.
after adopting the technique to avoid sql injection
if(get_magic_quotes_gpc())
{
$username = stripslashes($_POST[username]);
$email =stripslashes($_POST[email]);
}
else
{
$username = $_POST[username];
$email =$_POST[email];
}
previously my select and insert query were
INSERT INTO individuals(username, email) values('$username', '$email')
Select username from individuals where username = '$username'
presently the insert query is
$insertquery = sprintf(INSERT INTO individuals (username, email) VALUES
('%s', '%s'),
mysql_real_escape_string($username), mysql_real_escape_string($email));
This insert query is working however the select query is not doing its task
as before of checking if the username already exists or not, even if i
register with the same username again it does not alert that the username
exists.
the select query is
$selectqueryusername = sprintf(Select username from individuals where
username='%s', mysql_real_escape_string($username));
should i change the syntax of the above select query or is there something
else in need to do to fix the select query.
please advice.
thanks.
[PHP] sql syntax problem
Hello everybody, I am trying to create a sql query with php and I do have a syntax problem with the mysql query. One row is called plz and I would like to search for a value inside that with a like statement. Problem is, the system takes the u.plz as a character not as a table element: LIKE %u.plz% Has anybody an idea how the correct sytnax is? I am lost here. Thank you in advance, Merlin PS: This is the complete statement: SELECT c.name AS city FROM geo_de.geodb_locations AS c, fix.user AS u WHERE u.user_id =4 AND c.plz LIKE %u.plz%; -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] sql syntax problem
Merlin wrote:
SELECT c.name AS city
FROM geo_de.geodb_locations AS c, fix.user AS u
WHERE u.user_id =4 AND c.plz
LIKE %u.plz%;
I believe you want something not unlike this:
WHERE u.user_id = 4
AND c.plz LIKE concat('%', u.plz, '%')
--
Like Music?
http://l-i-e.com/artists.htm
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] sql syntax problem
This is the PHP mailing list, not the SQL mailing list ;) but here is the syntax ( not tested ): SELECT c.name AS city FROM geodb_locations AS c, user AS u WHERE u.user_id = 4 AND c.plz LIKE u.plz; Hello everybody, I am trying to create a sql query with php and I do have a syntax problem with the mysql query. One row is called plz and I would like to search for a value inside that with a like statement. Problem is, the system takes the u.plz as a character not as a table element: LIKE %u.plz% Has anybody an idea how the correct sytnax is? I am lost here. Thank you in advance, Merlin PS: This is the complete statement: SELECT c.name AS city FROM geo_de.geodb_locations AS c, fix.user AS u WHERE u.user_id =4 AND c.plz LIKE %u.plz%; -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Further help for PHP, SQL syntax, and register_globals
Sugimoto wrote:
Bad query: You have an error in your SQL syntax near 'and Tit like and Aut
like and Auty like ' at line 4
[snip]
foreach ($_GET as $value) {
if (empty($value)) $value = %;
You have an issue here. You're looping through $_GET and attempting to
set a default value (which is good), but you're not making any changes
to $_GET at all, just resetting the same $value variable to '%'.
foreach($_GET as $key=$value)
{
if(empty($value))
{ $_GET[$key] = '%'; }
}
--
---John Holmes...
Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/
php|architect: The Magazine for PHP Professionals www.phparch.com
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Further help for PHP, SQL syntax, and register_globals
To view the terms under which this email is distributed, please go to
http://disclaimer.leedsmet.ac.uk/email.htm
On 26 October 2004 12:01, John Holmes wrote:
Sugimoto wrote:
Bad query: You have an error in your SQL syntax near 'and Tit like
and Aut like and Auty like ' at line 4
[snip]
foreach ($_GET as $value) {
if (empty($value)) $value = %;
You have an issue here. You're looping through $_GET and attempting to
set a default value (which is good), but you're not making
any changes
to $_GET at all, just resetting the same $value variable to '%'.
foreach($_GET as $key=$value)
{
if(empty($value))
{ $_GET[$key] = '%'; }
}
Or, if I've been paying attention properly (which I might easily not have!
;), in PHP 5 you can do:
foreach ($_GET as $value) {
if (empty($value)) $value = %;
}
Cheers!
Mike
-
Mike Ford, Electronic Information Services Adviser,
Learning Support Services, Learning Information Services,
JG125, James Graham Building, Leeds Metropolitan University,
Headingley Campus, LEEDS, LS6 3QS, United Kingdom
Email: [EMAIL PROTECTED]
Tel: +44 113 283 2600 extn 4730 Fax: +44 113 283 3211
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL syntax error in PHP script. dunno what's wrong
insert into test values (0,''; DELETE FROM test; ',1); ERROR 1064: You have an error in your SQL syntax near '' at line 1 what about insert into test values (0,'\'; DELETE FROM test; ',1); the character ' is used to denote the beginning and the end of a field value. If you have this character within the value, you will have to escape it. It's as simple as that. -- Herzlich Werner Stuerenburg _ ISIS Verlag, Teut 3, D-32683 Barntrup-Alverdissen Tel 0(049) 5224-997 407 · Fax 0(049) 5224-997 409 http://pferdezeitung.de -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP] SQL syntax error in PHP script. dunno what's wrong
Thanks everybody, and Sam, that makes sense WRT the multiple words causing troubles. On Wed, 1 Aug 2001 12:54:01 -0400, Sam Masiello wrote: You will need to put single quotes around your variables in your SQL statement. Like this: $sql = UPDATE TABLE seminar SET title='$title',speaker='$speaker',event_date='$tdate',time='$time',bldg='$bu ilding' ,rm='$room' WHERE id='$id'; Without the quotes, SQL doesn't know that Something Amazing is supposed to go together in the same string. HTH Sam Masiello Software Quality Assurance Engineer Synacor (716) 853-1362 x289 [EMAIL PROTECTED] -Original Message- From: Chris Worth [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 01, 2001 12:36 PM To:[EMAIL PROTECTED] Subject: [PHP] SQL syntax error in PHP script. dunno what's wrong hey gang. here is my sql statement from my php script. $sql = UPDATE TABLE seminar SET title=$title,speaker=$speaker,event_date=$tdate,time=$time,bldg=$building ,rm=$room WHERE id=$id; it appears just like that in my code. here is the $sql string echoed to the screen to verify the variables. UPDATE TABLE seminar SET title=Something amazing,speaker=Mr. Black,event_date=2001-08-05,time=11:00:am,bldg=BCC ,rm=201 WHERE id=48 all of the variable are valid. here is my error. 1064: You have an error in your SQL syntax near 'TABLE seminar SET title=Something amazing,speaker=Mr. Black,event_date=2001-08' at line 1 I'm baffled. and I couldn't find error 1064 in the mysql manual.pdf either. any ideas. I know this is going to turn out to be something silly. thanks, chris -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP] SQL syntax error in PHP script. dunno what's wrong
I'd always understood that mysql doesn't allow multiple statements to be submitted so this post obviously worried me. I did some tests and confirmed that this is not a problem in MySQL queries from PHP. If I'm wrong about this please let me know. Tim -- From: scott [gts] [SMTP:[EMAIL PROTECTED]] Sent: 01 August 2001 18:03 To: php Subject: RE: [PHP] SQL syntax error in PHP script. dunno what's wrong no offense to you sam, but please dont ever simply place single quotes around values. you have to escape the values *themselves*. what if someone submitted the form field title as: $title = '; DELETE FROM seminar; if you didn't escape the single quotes in there, it would get interpreted as a valid DELETE statement and your seminar table would get wiped. however, if you escaped $title, you'd end up setting title to \'; DELETE FROM SEMINAR; (rather than have the contents of $title interpreted as SQL commands) -Original Message- From: Sam Masiello [mailto:[EMAIL PROTECTED]] Subject: RE: [PHP] SQL syntax error in PHP script. dunno what's wrong You will need to put single quotes around your variables in your SQL statement. Like this: $sql = UPDATE TABLE seminar SET title='$title',speaker='$speaker',event_date='$tdate',time='$time',bldg='$bu ilding' ,rm='$room' WHERE id='$id'; Without the quotes, SQL doesn't know that Something Amazing is supposed to go together in the same string. HTH -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] SQL syntax error in PHP script. dunno what's wrong
hmmm, i am seeing the same thing as tim here. are we doing something wrong? i created a test table, entered some dummy data and then using scott's example of '; DELETE FROM seminar; i tried executing insert into test values (0,''; DELETE FROM test; ',1); and got this error ERROR 1064: You have an error in your SQL syntax near '' at line 1 trying other variations either caused similar errors or added rows to my table. is it possible to sneak in a command in this manner? mike on 8/2/01 2:39 AM, Tim Ward at [EMAIL PROTECTED] wrote: I'd always understood that mysql doesn't allow multiple statements to be submitted so this post obviously worried me. I did some tests and confirmed that this is not a problem in MySQL queries from PHP. If I'm wrong about this please let me know. Tim -- From: scott [gts] [SMTP:[EMAIL PROTECTED]] Sent: 01 August 2001 18:03 To: php Subject: RE: [PHP] SQL syntax error in PHP script. dunno what's wrong no offense to you sam, but please dont ever simply place single quotes around values. you have to escape the values *themselves*. what if someone submitted the form field title as: $title = '; DELETE FROM seminar; if you didn't escape the single quotes in there, it would get interpreted as a valid DELETE statement and your seminar table would get wiped. however, if you escaped $title, you'd end up setting title to \'; DELETE FROM SEMINAR; (rather than have the contents of $title interpreted as SQL commands) -Original Message- From: Sam Masiello [mailto:[EMAIL PROTECTED]] Subject: RE: [PHP] SQL syntax error in PHP script. dunno what's wrong You will need to put single quotes around your variables in your SQL statement. Like this: $sql = UPDATE TABLE seminar SET title='$title',speaker='$speaker',event_date='$tdate',time='$time',bldg='$bu ilding' ,rm='$room' WHERE id='$id'; Without the quotes, SQL doesn't know that Something Amazing is supposed to go together in the same string. HTH -- mike cullerton -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] SQL syntax error in PHP script. dunno what's wrong
on 8/1/01 11:35 AM, Chris Worth at [EMAIL PROTECTED] wrote: hey gang. here is my sql statement from my php script. $sql = UPDATE TABLE seminar SET title=$title,speaker=$speaker,event_date=$tdate,time=$time,bldg=$building ,rm=$room WHERE id=$id; strings in a mysql query need to be quoted. So change it to $sql = UPDATE TABLE seminar SET title='$title',speaker='$speaker',...; Matt -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP] SQL syntax error in PHP script. dunno what's wrong
You will need to put single quotes around your variables in your SQL statement. Like this: $sql = UPDATE TABLE seminar SET title='$title',speaker='$speaker',event_date='$tdate',time='$time',bldg='$bu ilding' ,rm='$room' WHERE id='$id'; Without the quotes, SQL doesn't know that Something Amazing is supposed to go together in the same string. HTH Sam Masiello Software Quality Assurance Engineer Synacor (716) 853-1362 x289 [EMAIL PROTECTED] -Original Message- From: Chris Worth [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 01, 2001 12:36 PM To: [EMAIL PROTECTED] Subject:[PHP] SQL syntax error in PHP script. dunno what's wrong hey gang. here is my sql statement from my php script. $sql = UPDATE TABLE seminar SET title=$title,speaker=$speaker,event_date=$tdate,time=$time,bldg=$building ,rm=$room WHERE id=$id; it appears just like that in my code. here is the $sql string echoed to the screen to verify the variables. UPDATE TABLE seminar SET title=Something amazing,speaker=Mr. Black,event_date=2001-08-05,time=11:00:am,bldg=BCC ,rm=201 WHERE id=48 all of the variable are valid. here is my error. 1064: You have an error in your SQL syntax near 'TABLE seminar SET title=Something amazing,speaker=Mr. Black,event_date=2001-08' at line 1 I'm baffled. and I couldn't find error 1064 in the mysql manual.pdf either. any ideas. I know this is going to turn out to be something silly. thanks, chris -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP] SQL syntax error in PHP script. dunno what's wrong
*always always always* quote everything in SQL statements.
you run the risk of letting people insert arbitrary
SQL statements into your script if you dont quote values.
if you're using MySQL, try mysql_escape_string
http://php.net/manual/en/function.mysql-escape-string.php
or you could roll your own with relative ease:
function db_quote($value) {
return '. preg_replace(/'/, '', $value) .'
}
-Original Message-
From: Matt Greer [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, August 01, 2001 12:45 PM
To: [EMAIL PROTECTED]
Subject: Re: [PHP] SQL syntax error in PHP script. dunno what's wrong
on 8/1/01 11:35 AM, Chris Worth at [EMAIL PROTECTED] wrote:
hey gang.
here is my sql statement from my php script.
$sql = UPDATE TABLE seminar SET
title=$title,speaker=$speaker,event_date=$tdate,time=$time,bldg=$building
,rm=$room WHERE id=$id;
strings in a mysql query need to be quoted. So change it to
$sql = UPDATE TABLE seminar SET title='$title',speaker='$speaker',...;
Matt
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP] SQL syntax error in PHP script. dunno what's wrong
no offense to you sam, but please dont ever simply place single quotes around values. you have to escape the values *themselves*. what if someone submitted the form field title as: $title = '; DELETE FROM seminar; if you didn't escape the single quotes in there, it would get interpreted as a valid DELETE statement and your seminar table would get wiped. however, if you escaped $title, you'd end up setting title to \'; DELETE FROM SEMINAR; (rather than have the contents of $title interpreted as SQL commands) -Original Message- From: Sam Masiello [mailto:[EMAIL PROTECTED]] Subject: RE: [PHP] SQL syntax error in PHP script. dunno what's wrong You will need to put single quotes around your variables in your SQL statement. Like this: $sql = UPDATE TABLE seminar SET title='$title',speaker='$speaker',event_date='$tdate',time='$time',bldg='$bu ilding' ,rm='$room' WHERE id='$id'; Without the quotes, SQL doesn't know that Something Amazing is supposed to go together in the same string. HTH -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
