Re: [PHP] About Buggy SQL Query
Chris wrote:
Kelvin Park wrote:
mySQL database becomes inaccessible after a buggy sql string gets
queried.
The SQL server runs fine, however it seems like just the database is
being
looped infinitely so to say.
Here is an example:
(PHP)
$sql = SELECT * FROM DB_TABLE WHERE PR_NUMBER = $whatever, DFLJJ =
$SD;LOOE; (-- invalid sql query string)
mysql_query($sql);
When this query string is queried during the (webpage) loading
process, the
webpage just gets timed out without any error nor warning messages.
Does anyone know if there is a certain way to prevent mysql database
from
stalling due to buggy sql strings?
use mysql_real_escape_string to stop it from happening.
I've tried the mysql_real_escape_string, however it seemed like it was
working well at first, but the problem is that when I do the following
query, the database crashes:
$query = SELECT * FROM PRODUCT_TABLE WHERE MATCH (product, description)
AGAINST('whatever') OR MATCH(categoryname) AGAINST('whatever');
It seems like putting two match functions in the same query might have
caused the crash.
My question is, how could I immediately just have one of my databases in
the Database Server restarted (w/o affecting any of the data)?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] About Buggy SQL Query
Kelvin Park wrote:
Chris wrote:
Kelvin Park wrote:
mySQL database becomes inaccessible after a buggy sql string gets
queried.
The SQL server runs fine, however it seems like just the database is
being
looped infinitely so to say.
Here is an example:
(PHP)
$sql = SELECT * FROM DB_TABLE WHERE PR_NUMBER = $whatever, DFLJJ =
$SD;LOOE; (-- invalid sql query string)
mysql_query($sql);
When this query string is queried during the (webpage) loading
process, the
webpage just gets timed out without any error nor warning messages.
Does anyone know if there is a certain way to prevent mysql database
from
stalling due to buggy sql strings?
use mysql_real_escape_string to stop it from happening.
I've tried the mysql_real_escape_string, however it seemed like it was
working well at first, but the problem is that when I do the following
query, the database crashes:
$query = SELECT * FROM PRODUCT_TABLE WHERE MATCH (product, description)
AGAINST('whatever') OR MATCH(categoryname) AGAINST('whatever');
It seems like putting two match functions in the same query might have
caused the crash.
Why are they separate? Just include another field in the first match part.
If that's not an option, union the results:
select * from table where match(product) against('whatever')
union all
select * from table where match(categoryname) against('whatever')
See http://dev.mysql.com/doc/refman/4.1/en/union.html
--
Postgresql php tutorials
http://www.designmagick.com/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] About Buggy SQL Query
Chris wrote: Kelvin Park wrote: mySQL database becomes inaccessible after a buggy sql string gets queried. The SQL server runs fine, however it seems like just the database is being looped infinitely so to say. Here is an example: (PHP) $sql = SELECT * FROM DB_TABLE WHERE PR_NUMBER = $whatever, DFLJJ = $SD;LOOE; (-- invalid sql query string) mysql_query($sql); When this query string is queried during the (webpage) loading process, the webpage just gets timed out without any error nor warning messages. Does anyone know if there is a certain way to prevent mysql database from stalling due to buggy sql strings? use mysql_real_escape_string to stop it from happening. Thanks, I looked over some comments posted on the PHP library web site under mysql_real_escape_string function. I didn't realize it is also used to aid sql injection prevention. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] About Buggy SQL Query
What is in $SD? And are you using mysql_real_escape_string on all values? On Mon, August 13, 2007 11:31 pm, Kelvin Park wrote: mySQL database becomes inaccessible after a buggy sql string gets queried. The SQL server runs fine, however it seems like just the database is being looped infinitely so to say. Here is an example: (PHP) $sql = SELECT * FROM DB_TABLE WHERE PR_NUMBER = $whatever, DFLJJ = $SD;LOOE; (-- invalid sql query string) mysql_query($sql); When this query string is queried during the (webpage) loading process, the webpage just gets timed out without any error nor warning messages. Does anyone know if there is a certain way to prevent mysql database from stalling due to buggy sql strings? -- Some people have a gift link here. Know what I want? I want you to buy a CD from some indie artist. http://cdbaby.com/browse/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] About Buggy SQL Query
mySQL database becomes inaccessible after a buggy sql string gets queried. The SQL server runs fine, however it seems like just the database is being looped infinitely so to say. Here is an example: (PHP) $sql = SELECT * FROM DB_TABLE WHERE PR_NUMBER = $whatever, DFLJJ = $SD;LOOE; (-- invalid sql query string) mysql_query($sql); When this query string is queried during the (webpage) loading process, the webpage just gets timed out without any error nor warning messages. Does anyone know if there is a certain way to prevent mysql database from stalling due to buggy sql strings?
Re: [PHP] About Buggy SQL Query
Kelvin Park wrote: mySQL database becomes inaccessible after a buggy sql string gets queried. The SQL server runs fine, however it seems like just the database is being looped infinitely so to say. Here is an example: (PHP) $sql = SELECT * FROM DB_TABLE WHERE PR_NUMBER = $whatever, DFLJJ = $SD;LOOE; (-- invalid sql query string) mysql_query($sql); When this query string is queried during the (webpage) loading process, the webpage just gets timed out without any error nor warning messages. Does anyone know if there is a certain way to prevent mysql database from stalling due to buggy sql strings? use mysql_real_escape_string to stop it from happening. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] can't output sql query with php code.
Paul Goepfert wrote:
This is the full code for the code that doesn't work.
?php
echo 'select name=month \n';
$month_query = mysql_query(SELECT m_id, months FROM Month);
while ($r = mysql_fetch_array($month_query));
{
$v = $r[m_id];
$out = $r[months];
echo option value=$v$out/option\n;
}
echo '/select\n';
?
dear sir,
overall, i can see no visible error. but u must debug the process.
1. please add print_r($r) within the while loop to see the contents.
2. check the num of rows returned by mysql_query.
3. check it mysql_query is not returning an error.
--
Sumeet Shroff
http://www.prateeksha.com
Web Design and Ecommerce Development, Mumbai India
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] can't output sql query with php code.
?php
echo 'select name=month \n';
$month_query = mysql_query(SELECT m_id, months FROM Month);
while ($r = mysql_fetch_array($month_query));
Remove the semi-colon at the end of the above line and it works like a charm.
{
$v = $r[m_id];
$out = $r[months];
echo option value=$v$out/option\n;
}
echo '/select\n';
?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] can't output sql query with php code.
Thanks everyone for your help. I should have seen the semi-colon at
the end of the while loop. I must have looked at the code 20 times
and I can't believe I missed it.
Paul
On 2/6/06, Brady Mitchell [EMAIL PROTECTED] wrote:
?php
echo 'select name=month \n';
$month_query = mysql_query(SELECT m_id, months FROM Month);
while ($r = mysql_fetch_array($month_query));
Remove the semi-colon at the end of the above line and it works like a charm.
{
$v = $r[m_id];
$out = $r[months];
echo option value=$v$out/option\n;
}
echo '/select\n';
?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] can't output sql query with php code.
It always helps to have a different point of view to look at code sometimes.
Else you'll go bonkers looking for that stupid semi-colon! :)
On 2/6/06 2:18 AM, Paul Goepfert [EMAIL PROTECTED] wrote:
Thanks everyone for your help. I should have seen the semi-colon at
the end of the while loop. I must have looked at the code 20 times
and I can't believe I missed it.
Paul
On 2/6/06, Brady Mitchell [EMAIL PROTECTED] wrote:
?php
echo 'select name=month \n';
$month_query = mysql_query(SELECT m_id, months FROM Month);
while ($r = mysql_fetch_array($month_query));
Remove the semi-colon at the end of the above line and it works like a charm.
{
$v = $r[m_id];
$out = $r[months];
echo option value=$v$out/option\n;
}
echo '/select\n';
?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] can't output sql query with php code.
Hi all,
I have a mysql database setup to use with my php web page. I have
been able to access the database and get values into my drop down
menus. However when I tried to output the contents into two two
diferent variables the code does not work. To be more specific I will
show you the code both the one that works and the one that doesn't
Doesn't work
-
?php
$month_query = mysql_query(SELECT m_id, months FROM Month);
while ($row = mysql_fetch_array($month_query))
{
$val = $row[m_id];
$out = $row[months];
echo option value=$val$out/option;
}
?
-
Code that works
-
?php
echo ' select name=equipment size=1 id=equip
onChange=addRow()';
$Equip_query = mysql_query(SELECT equip FROM Equipment ORDER BY
equip ASC);
while ($r = mysql_fetch_array($Equip_query))
{
$val = $r[equip];
echo option value=$val$val/option\n;
}
echo /select/td;
?
Also I should tell you that the code that works comes after the code
that doesn't work in my web page.
Oh for the values that are outputted in the code that doesn't work are
val is numeric and out is a string.
I know my sql query works because I tested it in mysql.
Thanks for the help
Paul
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] can't output sql query with php code.
Hi Paul,
If equip contains a space it will probably break the dropdown.
Change it to:
echo option value=' . htmlspecialchars($val) . '$val/option\n;
in case it has quotes, spaces, or in it.
Paul Goepfert wrote:
Hi all,
I have a mysql database setup to use with my php web page. I have
been able to access the database and get values into my drop down
menus. However when I tried to output the contents into two two
diferent variables the code does not work. To be more specific I will
show you the code both the one that works and the one that doesn't
Doesn't work
-
?php
$month_query = mysql_query(SELECT m_id, months FROM Month);
while ($row = mysql_fetch_array($month_query))
{
$val = $row[m_id];
$out = $row[months];
echo option value=$val$out/option;
}
?
-
Code that works
-
?php
echo ' select name=equipment size=1 id=equip
onChange=addRow()';
$Equip_query = mysql_query(SELECT equip FROM Equipment ORDER BY
equip ASC);
while ($r = mysql_fetch_array($Equip_query))
{
$val = $r[equip];
echo option value=$val$val/option\n;
}
echo /select/td;
?
Also I should tell you that the code that works comes after the code
that doesn't work in my web page.
Oh for the values that are outputted in the code that doesn't work are
val is numeric and out is a string.
I know my sql query works because I tested it in mysql.
Thanks for the help
Paul
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] can't output sql query with php code.
Doesn't work
-
?php
$month_query = mysql_query(SELECT m_id, months FROM Month);
while ($row = mysql_fetch_array($month_query))
{
$val = $row[m_id];
$out = $row[months];
echo option value=$val$out/option;
}
?
You're missing the select tags.
while ($r = mysql_fetch_array($Equip_query))
{
$val = $r[equip];
echo option value=$val$val/option\n;
}
On a seperate note, if all you are doing with the $val variable is using it to
echo, it's not needed. Put brackets { } around the var to echo ie:
{$r[equip]} - makes it easier to read later when you come back to tweak your
code.
Brady
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] can't output sql query with php code.
This is the full code for the code that doesn't work.
?php
echo 'select name=month \n';
$month_query = mysql_query(SELECT m_id, months FROM Month);
while ($r = mysql_fetch_array($month_query));
{
$v = $r[m_id];
$out = $r[months];
echo option value=$v$out/option\n;
}
echo '/select\n';
?
On 2/6/06, Brady Mitchell [EMAIL PROTECTED] wrote:
Doesn't work
-
?php
$month_query = mysql_query(SELECT m_id, months FROM Month);
while ($row = mysql_fetch_array($month_query))
{
$val = $row[m_id];
$out = $row[months];
echo option value=$val$out/option;
}
?
You're missing the select tags.
while ($r = mysql_fetch_array($Equip_query))
{
$val = $r[equip];
echo option value=$val$val/option\n;
}
On a seperate note, if all you are doing with the $val variable is using it
to echo, it's not needed. Put brackets { } around the var to echo ie:
{$r[equip]} - makes it easier to read later when you come back to tweak your
code.
Brady
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Forming an SQL query
Hi, Thanks to everyone's help, that multipage monster of a form is now working properly (yay!). One problem I have though is that I stick the answers as each page is completed into a table. If the user hits the back button, rather than adding a new row to the table I'd rather update it if it's there. That's fairly straightforward when it's 1:1 questions to answers: $q_a_sql='INSERT INTO $userAnswerTable ( q_id, a_id ) VALUES ' . (implode(,,$qanda)) . ' on duplicate key UPDATE a_id = VALUES(a_id);'; But when it's 1:n, such as with checkboxes, this neat little plan of mine is thwarted. So if I change the userAnswerTable to three columns, u_id, q_id and a_id, is there a way I can do all the 1:1 and 1:n in the manner I wish? TIA, JJ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Forming an SQL query
Ah, I had left out the third column the first time! Thanks.
Now I can insert and not create dupes but for some reason it is not
updating.
Here's the code:
if ( ($_POST['action'] == 'process') (!sizeof($message) ) )
{
foreach($_POST as $key=$val)
{
//find key/val sets within posts which are both numeric
if(is_numeric($key) is_numeric($val))
{
$nkey=$key;
//add these values ( q_id, a_id ) to sql statement
$qanda[] .= ('1' , ' . $nkey . ' , ' . $val .
');
}
//find key/val sets within sub-arrays of $_POST
which are numeric
if(is_array($val))
{
foreach ($val as $akey=$aval)
{
//add these values ( q_id, a_id ) to sql
statement
$qanda[] .= ('1' , ' . $key . ' , ' .
$aval . ');
}
}
}
$q_a_sql=INSERT INTO . $userTable . (u_id, q_id, a_id )
VALUES . (implode(,,$qanda)) . on duplicate key
UPDATE a_id = VALUES(a_id);;
if($q_a_result = mysql_query($q_a_sql))
{
unset($_SESSION['required_fields']);
$cat = $_POST['cat']+1;
include_once(QUESTIONS . 'q.inc');
}
?
Kristen G. Thorson wrote:
How is it your plan thwarted? It looks fine to me, but maybe I'm
missing something. The only thing I can think is that you're not
defining your table keys correctly to correctly use ON DUPLICATE KEY.
Do you have a key defined for all three columns *together*?
mysql create table user_answers (u_id int(11) not null, q_id int(11)
not null, a_id int(11) not null, unique( u_id, q_id, a_id ) );
Query OK 0 rows affected (0.22 sec)
mysql insert into user_answers (u_id,q_id,a_id) values
(1,1,1),(1,1,2),(1,2,1),(1,1,1) on duplicate key update a_id=values(a_id);
Query OK, 5 rows affected (0.01 sec)
Records: 4 Duplicates: 1 Warnings: 0
mysqlselect * from user_answers;
+--+--+--+
| u_id | q_id | a_id |
+--+--+--+
|1 |1 |1 |
|1 |1 |2 |
|1 |1 |3 |
+--+--+--+
3 rows in set (0.00 sec)
So, three different answers for the same user same question. The one
duplicate did not cause an error because of the ON DUPLICATE KEY. This
looks like it's what you're trying to do, so then what's your error?
kgt
Jack Jackson wrote:
Hi,
Thanks to everyone's help, that multipage monster of a form is now
working properly (yay!).
One problem I have though is that I stick the answers as each page is
completed into a table. If the user hits the back button, rather than
adding a new row to the table I'd rather update it if it's there.
That's fairly straightforward when it's 1:1 questions to answers:
$q_a_sql='INSERT INTO $userAnswerTable
( q_id, a_id )
VALUES ' . (implode(,,$qanda))
. ' on duplicate key UPDATE a_id = VALUES(a_id);';
But when it's 1:n, such as with checkboxes, this neat little plan of
mine is thwarted.
So if I change the userAnswerTable to three columns, u_id, q_id and
a_id, is there a way I can do all the 1:1 and 1:n in the manner I wish?
TIA,
JJ
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Help with SQL Query String
Ave, Simple problem.. Following is a statement I¹m inserting in my PHP Script... But it doesn¹t work because I¹m not able to figure out how to put the variables with the trim() function in the SQL insert statement: $sql = INSERT INTO tblname (USERID,FULLNAME,SSN,STARTDATE) VALUES (trim($row[USERID]),trim($row[FULLNAME]),trim($row[SSNO]),trim($row[STARTDAT E])); I can of course predefine a unique variables for each of the $row[] variables with the trim() function and then use those variables in my INSERT statement... But I¹m sure there is a way this statement can work, I¹m just not able to figure it out and would love if anyway made a suggestion. Thanks, Rahul S. Johari Coordinator, Internet Administration Informed Marketing Services Inc. 251 River Street Troy, NY 12180 Tel: (518) 266-0909 x154 Fax: (518) 266-0909 Email: [EMAIL PROTECTED] http://www.informed-sources.com
RE: [PHP] Help with SQL Query String
[snip] $sql = INSERT INTO tblname (USERID,FULLNAME,SSN,STARTDATE) VALUES (trim($row[USERID]),trim($row[FULLNAME]),trim($row[SSNO]),trim($row[STAR TDAT E])); [/snip] Time to quote and concatenate and make pretty... $sql = INSERT INTO tblname (USERID,FULLNAME,SSN,STARTDATE) ; $sql .= VALUES ( ; $sql .= ' . trim($row['USERID']) . ', ; $sql .= ' . trim($row['FULLNAME']) . ', ; $sql .= ' . trim($row['SSNO']) . ', ; $sql .= ' . trim($row['STARTDATE']) . ' ; $sql .= ) ; This will make things easier to maintain as well. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Help with SQL Query String
You should not just give him the code but rather tell him why. 1. trim() is a php function. MySQL does not know what to do with it. You need to place it 'outside' of the sql. You can also do something like this: $userid = trim($row['USERID']); Then use $userid in your sql. 2. Items in arrays must be in quotes. jzf Jay Blanchard wrote: [snip] $sql = INSERT INTO tblname (USERID,FULLNAME,SSN,STARTDATE) VALUES (trim($row[USERID]),trim($row[FULLNAME]),trim($row[SSNO]),trim($row[STAR TDAT E])); [/snip] Time to quote and concatenate and make pretty... $sql = INSERT INTO tblname (USERID,FULLNAME,SSN,STARTDATE) ; $sql .= VALUES ( ; $sql .= ' . trim($row['USERID']) . ', ; $sql .= ' . trim($row['FULLNAME']) . ', ; $sql .= ' . trim($row['SSNO']) . ', ; $sql .= ' . trim($row['STARTDATE']) . ' ; $sql .= ) ; This will make things easier to maintain as well. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Help with SQL Query String
[snip]
You should not just give him the code but rather tell him why.
1. trim() is a php function. MySQL does not know what to do with it. You
need to place it 'outside' of the sql. You can also do something like
this:
$userid = trim($row['USERID']);
Then use $userid in your sql.
2. Items in arrays must be in quotes.
[/snip]
Ya'll bitch when I make them RTFM, ya'll bitch when I do codewhat's
a guy to do? j/k
Actually http://dev.mysql.com/doc/mysql/en/string-functions.html shows
that MySQL also has a trim function which could be applied thusly;
$sql = INSERT INTO tblname (USERID,FULLNAME,SSN,STARTDATE) ;
$sql .= VALUES ( ;
$sql .= TRIM(' . $row['USERID'] . '), ;
$sql .= TRIM(' . $row['FULLNAME'] . '), ;
$sql .= TRIM(' . $row['SSNO'] . '), ;
$sql .= TRIM(' . $row['STARTDATE'] . ') ;
$sql .= ) ;
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Help with SQL Query String
Jay Blanchard wrote: Ya'll bitch when I make them RTFM, ya'll bitch when I do codewhat's a guy to do? j/k Don't get married? ;) -- John C. Nichel ÜberGeek KegWorks.com 716.856.9675 [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Help with SQL Query String
Ave,
Thanks a lot folks.
I did actually mention that doing something like
$userid = trim($row['USERID']);
And then using those variables in my SQL statement would work... The only
reason I chose to make this post however and not do that was because I
wanted to know if it can be done the other way. I do understand though that
using the PHP trim() function in an SQL statemnt won't work.
However the new snip about mySQL having it's own TRIM() function is also
pretty cool.
Thanks again all,
Rahul S. Johari
Coordinator, Internet Administration
Informed Marketing Services Inc.
251 River Street
Troy, NY 12180
Tel: (518) 266-0909 x154
Fax: (518) 266-0909
Email: [EMAIL PROTECTED]
http://www.informed-sources.com
On 3/31/05 10:30 AM, Jay Blanchard [EMAIL PROTECTED]
wrote:
[snip]
You should not just give him the code but rather tell him why.
1. trim() is a php function. MySQL does not know what to do with it. You
need to place it 'outside' of the sql. You can also do something like
this:
$userid = trim($row['USERID']);
Then use $userid in your sql.
2. Items in arrays must be in quotes.
[/snip]
Ya'll bitch when I make them RTFM, ya'll bitch when I do codewhat's
a guy to do? j/k
Actually http://dev.mysql.com/doc/mysql/en/string-functions.html shows
that MySQL also has a trim function which could be applied thusly;
$sql = INSERT INTO tblname (USERID,FULLNAME,SSN,STARTDATE) ;
$sql .= VALUES ( ;
$sql .= TRIM(' . $row['USERID'] . '), ;
$sql .= TRIM(' . $row['FULLNAME'] . '), ;
$sql .= TRIM(' . $row['SSNO'] . '), ;
$sql .= TRIM(' . $row['STARTDATE'] . ') ;
$sql .= ) ;
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Help with SQL Query String
well...i would have told him to go pound sand...the php manual is great
and so is the MySQL manual. People are just lazy.
[/snip]
Ya'll bitch when I make them RTFM, ya'll bitch when I do codewhat's
a guy to do? j/k
Actually http://dev.mysql.com/doc/mysql/en/string-functions.html shows
that MySQL also has a trim function which could be applied thusly;
$sql = INSERT INTO tblname (USERID,FULLNAME,SSN,STARTDATE) ;
$sql .= VALUES ( ;
$sql .= TRIM(' . $row['USERID'] . '), ;
$sql .= TRIM(' . $row['FULLNAME'] . '), ;
$sql .= TRIM(' . $row['SSNO'] . '), ;
$sql .= TRIM(' . $row['STARTDATE'] . ') ;
$sql .= ) ;
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Help with SQL Query String
On 3/31/05 12:45 PM, Joseph Connolly [EMAIL PROTECTED] wrote:
well...i would have told him to go pound sand...the php manual is great
and so is the MySQL manual. People are just lazy.
Ave,
Pound sand .. Interesting.
And yes, both the manuals are great, and people are extremely lazy and
purposely idiotic, nonsensical and absurdly inconclusive of the inadequacies
of their lifeless brains.
Have an awesome day.
PS: Jay.. The code works great... Thanks a ton again!
Rahul S. Johari
Coordinator, Internet Administration
Informed Marketing Services Inc.
251 River Street
Troy, NY 12180
Tel: (518) 266-0909 x154
Fax: (518) 266-0909
Email: [EMAIL PROTECTED]
http://www.informed-sources.com
[/snip]
Ya'll bitch when I make them RTFM, ya'll bitch when I do codewhat's
a guy to do? j/k
Actually http://dev.mysql.com/doc/mysql/en/string-functions.html shows
that MySQL also has a trim function which could be applied thusly;
$sql = INSERT INTO tblname (USERID,FULLNAME,SSN,STARTDATE) ;
$sql .= VALUES ( ;
$sql .= TRIM(' . $row['USERID'] . '), ;
$sql .= TRIM(' . $row['FULLNAME'] . '), ;
$sql .= TRIM(' . $row['SSNO'] . '), ;
$sql .= TRIM(' . $row['STARTDATE'] . ') ;
$sql .= ) ;
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] R: [PHP] SQL Query Not Kosher?
On 03 February 2004 13:45, Alessandro Vitale contributed these pearls of
wisdom:
try removing curly braces as follows:
$query = mysql_query(UPDATE stories SET status='approved'
WHERE story_id={$id}); |
Nothing wrong with the above, it's perfectly valid -- just a slightly
different way of writing:
$query = mysql_query(UPDATE stories SET status='approved'
WHERE story_id=${id});
Cheers!
Mike
--
Mike Ford, Electronic Information Services Adviser,
Learning Support Services, Learning Information Services,
JG125, James Graham Building, Leeds Metropolitan University,
Beckett Park, 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
[PHP] R: [PHP] SQL Query Not Kosher?
try removing curly braces as follows:
$query = mysql_query(UPDATE stories SET status='approved' WHERE
story_id={$id});
|
|
|
\/
$query = mysql_query(UPDATE stories SET status='approved' WHERE
story_id=$id);
or
$query = mysql_query(UPDATE stories SET status='approved' WHERE
story_id=${id});
this applies if story_id is of type int in mysql table definition, or you
should enclose it among '' if is of type char, varchar or similar.
cheers
alessandro
-Messaggio originale-
Da: Mr. Austin [mailto:[EMAIL PROTECTED]
Inviato: martedì 3 febbraio 2004 5.35
A: [EMAIL PROTECTED]
Oggetto: [PHP] SQL Query Not Kosher?
Hi all:
I am trying to get this to work, but always get the same error: that the
resource in mysql_affected_rows() is not valid. Anyone see why this would
be? All variables have been tested for accuracy.
$query = mysql_query(UPDATE stories SET status='approved' WHERE
story_id={$id});
if(mysql_affected_rows($query) == 1) {
print(Your approval of \$title\ was successful. If this user entered
an email address, they have been sent a notice of its approval and
publication on the site.);
} else {
print(The approval of \$title\ was not successful. Please check with
the site administrator for assistance.);
}
The above SQL statement works perfectly with phpMyAdmin (and, oddly enough,
works with the above script, yet the Warning is produced and the 'not
successful' message is displayed) Any thoughts are appreciated!
Mr. Austin
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Inquiry on sql query class.
Hi. I am constructing a somewhat generic class to contain all queries needed to make a self sustaining base to conform for a larger purpose. Basically I am quite new to the concept of writing classes, which doesn´t have much to do with this inquiry though. One of the issues I ran into was how to handle possible multiple selections from an sql query without putting in a lot of if statements or switches to accomodate this. Instead I´ve taken the approach of using the explode/implode array handler to overcome this. Consider a very simple query: SELECT foo,bar FROM table ORDER BY foo asc;. Suppose the foo and bar was assigned to the array fields in the HTML form (the fields would of course be checked against the table and that sufficient privileges existed for the logged in user, before trying to perform the query.), would the $selected_fields = implode(,,$fields); followed by $sql_query = SELECT $selected_fields FROM table ORDER BY foo asc;; be a proper way of handling this issue or am I in over my head? Cheers, Henrik J.
[PHP] Maximum in SQL query?
Hi people, It seems that MySQL only accepts queries that are 470 characters or smaller when I use PHP. Is this a known thing? Can I change it somehow? I printed the query: Query: REPLACE INTO foto (ID, titel, fotograaf, origineel_formaat_x, origineel_formaat_y, datum_invoering, datum_wijziging, plaats_opname, datum_opname, file_thumb, file_large, url_thumb, url_large, thumb_hoogte, thumb_breedte, large_hoogte, large_breedte, fine_art_print, digitaal_formaat) VALUES (0001, Eva, Jasper, 1024, 768, now(), now(), Brunssum, , / var/www/html/foto/0/0/0/0/0001.thumb.jpg, /var/www/html/foto/0/0/0/0/0001.jpg, /foto/0/0/0/0/0001.thumb.jpg, / foto/0/0/0/0/0001.jpg, 200, 150, 500, 375, FALSE, Array) Database error: You have an error in your SQL syntax near ' /var/www/html/foto/0/0/0/0/0001.thumb.jpg, /var/www/html/ foto/0/0/0/0/0001.j' at line 1 -- Kind regards, Tim -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Maximum in SQL query?
[snip] It seems that MySQL only accepts queries that are 470 characters or smaller when I use PHP. Is this a known thing? Can I change it somehow? I printed the query: Query: REPLACE INTO foto (ID, titel, fotograaf, origineel_formaat_x, origineel_formaat_y, datum_invoering, datum_wijziging, plaats_opname, datum_opname, file_thumb, file_large, url_thumb, url_large, thumb_hoogte, thumb_breedte, large_hoogte, large_breedte, fine_art_print, digitaal_formaat) VALUES (0001, Eva, Jasper, 1024, 768, now(), now(), Brunssum, , / var/www/html/foto/0/0/0/0/0001.thumb.jpg, /var/www/html/foto/0/0/0/0/0001.jpg, /foto/0/0/0/0/0001.thumb.jpg, / foto/0/0/0/0/0001.jpg, 200, 150, 500, 375, FALSE, Array) Database error: You have an error in your SQL syntax near ' /var/www/html/foto/0/0/0/0/0001.thumb.jpg, /var/www/html/ foto/0/0/0/0/0001.j' at line 1 [/snip] I believe the error is a space prior to 'var' Brunssum, , / var/ try Brunssum, , /var/ I consistantly use queries in PHP that are far longer than 470 characters (not to mention that 470 would be an odd choice for a limit of this type.). HTH! Jay -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Maximum in SQL query?
Hi Jay, Jay Blanchard wrote: I believe the error is a space prior to 'var' Brunssum, , / var/ try Brunssum, , /var/ Nope, that wasn't the error, but the error was in there... it's the , , in there :) An empty value... Thanks for the quick reply! -- Kind regards, Tim -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Problem with SQL query
LIMIT was not included in the SQL92 SQL standards and very few vendors implement all of SQL99; the use of ANSI standards to promote portable programs has always been beset by this kind of problems. Wow! There's actually an SQL99 that vendors are targeting, kinda sorta? Hey, with any luck, but 2009, they'll all be SQL99-compliant, and we can start all over! Not that they ever all reached SQL92 compliance. Oh, just in case people don't know. SQL92 is named SQL92 because it was defined in... [drum roll] 1992 That's 100 years ago in doggie years :-) Consider yourself lucky if any of your SQL is really portable without jumping through hoops. I do want to apologize for my month_view/monthly_views typo. -- 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
[PHP] Problem with SQL query
I can't figure out what is wrong with this:
$query = select count(*) as monthly_views from visitors group by
extract('year', time), extract('month', time) order by monthly_view desc
limit 1;
$result = mysql_query($query);
echo $query;
$row = mysql_fetch_array($result);
echo $row['monthly_views'];
echo mysql_error();
I get this:
select count(*) as monthly_views from visitors group by extract('year',
time), extract('month', time) order by monthly_view desc limit 1
Warning: Supplied argument is not a valid MySQL result resource in
C:\Inetpub\TecEco_PHP\stats_interface\summary.php on line 76
You have an error in your SQL syntax near ''year', time), extract('month',
time) order by monthly_view desc limit 1' at line 1
I don't know any advanced SQL so I don't know how to debug this.
I am using mySQL. The person who gave me this code appeared to be using
postgreSQL if that makes any differance
--
JJ Harrison
[EMAIL PROTECTED]
www.tececo.com
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Problem with SQL query
ðÒÉ×ÅÔ!
I can't figure out what is wrong with this:
$query = select count(*) as monthly_views from visitors group by
extract('year', time), extract('month', time) order by monthly_view desc
limit 1;
AFAIK this is no ANSI SQL, which is why is not portable. Your query
should really be
SELECT
extract('year', time),
extract('month', time)
count(*) as monthly_views
FROM
visitors
group by
extract('year', time),
extract('month', time)
order by
monthly_view desc
limit 1
*BUT* extract doesn't seem to be a MYSQL function, you most probably
need to use DATE_FORMAT. Look in the Mysql docs for this.
As a general ANSI rule group by functions *need* the fields on which
result is grouped to appear *first* in the query. Take half an hour to
look at the GROUP BY docs in MySQL online manual, everything will become
much clearer.
ÐÏËÁ
áÌØÂÅÒÔÏ
ëÉÅ×
--
-_=}{=_-@-_=}{=_--_=}{=_-@-_=}{=_--_=}{=_-@-_=}{=_--_=}{=_-
LoRd, CaN yOu HeAr Me, LiKe I'm HeArInG yOu?
lOrD i'M sHiNiNg...
YoU kNoW I AlMoSt LoSt My MiNd, BuT nOw I'm HoMe AnD fReE
tHe TeSt, YeS iT iS
ThE tEsT, yEs It Is
tHe TeSt, YeS iT iS
ThE tEsT, yEs It Is...
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Problem with SQL query
ðÒÉ×ÅÔ!
I forgot to add:
SELECT
extract('year', time),
extract('month', time),
count(*) as monthly_views
FROM
visitors
group by
extract('year', time),
extract('month', time)
order by
monthly_view desc
limit 1
I take it that you have a *monthly_view* column in your table that is
not the *monthly_views* alias yoiu use in your query. if that's a typo
and you mean to order by the numebr of visitors (that is, you want the
most visited month on top of the result) your query should probably look
like:
SELECT
extract('year', time),
extract('month', time),
count(*) as monthly_views
FROM
visitors
group by
extract('year', time),
extract('month', time)
order by
3 desc
limit 1
That's because most databases (and I guess MySql is no exception) will
not be able to use an alias in their GROUP BY, ORDER BY clauses. But you
may want to try, maybe MySql *is* an exception, after all.
ÐÏËÁ
áÌØÂÅÒÔÏ
ëÉÅ×
--
-_=}{=_-@-_=}{=_--_=}{=_-@-_=}{=_--_=}{=_-@-_=}{=_--_=}{=_-
LoRd, CaN yOu HeAr Me, LiKe I'm HeArInG yOu?
lOrD i'M sHiNiNg...
YoU kNoW I AlMoSt LoSt My MiNd, BuT nOw I'm HoMe AnD fReE
tHe TeSt, YeS iT iS
ThE tEsT, yEs It Is
tHe TeSt, YeS iT iS
ThE tEsT, yEs It Is...
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Problem with SQL query
Thanks for giving me something to look up :)
--
JJ Harrison
[EMAIL PROTECTED]
www.tececo.com
Alberto Serra [EMAIL PROTECTED] wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
ðÒÉ×ÅÔ!
I forgot to add:
SELECT
extract('year', time),
extract('month', time),
count(*) as monthly_views
FROM
visitors
group by
extract('year', time),
extract('month', time)
order by
monthly_view desc
limit 1
I take it that you have a *monthly_view* column in your table that is
not the *monthly_views* alias yoiu use in your query. if that's a typo
and you mean to order by the numebr of visitors (that is, you want the
most visited month on top of the result) your query should probably look
like:
SELECT
extract('year', time),
extract('month', time),
count(*) as monthly_views
FROM
visitors
group by
extract('year', time),
extract('month', time)
order by
3 desc
limit 1
That's because most databases (and I guess MySql is no exception) will
not be able to use an alias in their GROUP BY, ORDER BY clauses. But you
may want to try, maybe MySql *is* an exception, after all.
ÐÏËÁ
áÌØÂÅÒÔÏ
ëÉÅ×
--
@-_=}{=_-@-_=}{=_-@-_=}{=_-@-_=}{=_-@-_=}{=_-@-_=}{=_-@-_=}{=_-@
LoRd, CaN yOu HeAr Me, LiKe I'm HeArInG yOu?
lOrD i'M sHiNiNg...
YoU kNoW I AlMoSt LoSt My MiNd, BuT nOw I'm HoMe AnD fReE
tHe TeSt, YeS iT iS
ThE tEsT, yEs It Is
tHe TeSt, YeS iT iS
ThE tEsT, yEs It Is...
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Problem with SQL query
No matter what I try I still seem to get the error message.
The coloumn that contains the dates has unix timestamps. I think this is why
it is not working.
I will research it further.
If you can be of any help please tell me.
--
JJ Harrison
[EMAIL PROTECTED]
www.tececo.com
Alberto Serra [EMAIL PROTECTED] wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
ðÒÉ×ÅÔ!
I forgot to add:
SELECT
extract('year', time),
extract('month', time),
count(*) as monthly_views
FROM
visitors
group by
extract('year', time),
extract('month', time)
order by
monthly_view desc
limit 1
I take it that you have a *monthly_view* column in your table that is
not the *monthly_views* alias yoiu use in your query. if that's a typo
and you mean to order by the numebr of visitors (that is, you want the
most visited month on top of the result) your query should probably look
like:
SELECT
extract('year', time),
extract('month', time),
count(*) as monthly_views
FROM
visitors
group by
extract('year', time),
extract('month', time)
order by
3 desc
limit 1
That's because most databases (and I guess MySql is no exception) will
not be able to use an alias in their GROUP BY, ORDER BY clauses. But you
may want to try, maybe MySql *is* an exception, after all.
ÐÏËÁ
áÌØÂÅÒÔÏ
ëÉÅ×
--
@-_=}{=_-@-_=}{=_-@-_=}{=_-@-_=}{=_-@-_=}{=_-@-_=}{=_-@-_=}{=_-@
LoRd, CaN yOu HeAr Me, LiKe I'm HeArInG yOu?
lOrD i'M sHiNiNg...
YoU kNoW I AlMoSt LoSt My MiNd, BuT nOw I'm HoMe AnD fReE
tHe TeSt, YeS iT iS
ThE tEsT, yEs It Is
tHe TeSt, YeS iT iS
ThE tEsT, yEs It Is...
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Problem with SQL query
I have fixed it now don't worry!
--
JJ Harrison
[EMAIL PROTECTED]
www.tececo.com
Alberto Serra [EMAIL PROTECTED] wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
ðÒÉ×ÅÔ!
I forgot to add:
SELECT
extract('year', time),
extract('month', time),
count(*) as monthly_views
FROM
visitors
group by
extract('year', time),
extract('month', time)
order by
monthly_view desc
limit 1
I take it that you have a *monthly_view* column in your table that is
not the *monthly_views* alias yoiu use in your query. if that's a typo
and you mean to order by the numebr of visitors (that is, you want the
most visited month on top of the result) your query should probably look
like:
SELECT
extract('year', time),
extract('month', time),
count(*) as monthly_views
FROM
visitors
group by
extract('year', time),
extract('month', time)
order by
3 desc
limit 1
That's because most databases (and I guess MySql is no exception) will
not be able to use an alias in their GROUP BY, ORDER BY clauses. But you
may want to try, maybe MySql *is* an exception, after all.
ÐÏËÁ
áÌØÂÅÒÔÏ
ëÉÅ×
--
@-_=}{=_-@-_=}{=_-@-_=}{=_-@-_=}{=_-@-_=}{=_-@-_=}{=_-@-_=}{=_-@
LoRd, CaN yOu HeAr Me, LiKe I'm HeArInG yOu?
lOrD i'M sHiNiNg...
YoU kNoW I AlMoSt LoSt My MiNd, BuT nOw I'm HoMe AnD fReE
tHe TeSt, YeS iT iS
ThE tEsT, yEs It Is
tHe TeSt, YeS iT iS
ThE tEsT, yEs It Is...
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Problem with SQL query
LIMIT was not included in the SQL92 SQL standards and very few vendors
implement all of SQL99; the use of ANSI standards to promote portable
programs has always been beset by this kind of problems.
Mark Charette
Former ANSI X3H3.1 member
-Original Message-
From: Alberto Serra [mailto:[EMAIL PROTECTED]]
SELECT
extract('year', time),
extract('month', time)
count(*) as monthly_views
FROM
visitors
group by
extract('year', time),
extract('month', time)
order by
monthly_view desc
limit 1
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Problem with SQL query
ðÒÉ×ÅÔ!
Mark Charette wrote:
LIMIT was not included in the SQL92 SQL standards and very few vendors
implement all of SQL99; the use of ANSI standards to promote portable
programs has always been beset by this kind of problems.
Yes, and vendors just love to have proprietary standards to protect
their market shares. The basic idea is that since you cannot just switch
from an engine to another without seriously risking your application
stability you will tolerate the yes, it's a known bug answer, whenever
your 100 thousand dollars application cannot print a simple data report
because trying to set fonts size will crash the current job. Or when
real numbers are returned with a different values from the one you wrote
in.
Not talking about MeAndMyFriendJoe'sXperimentalSQLMachineGun 0.0, That
was Oracle 7.3 with Developer (fonts) and Oracle 8.something under WinNT
(floating real number values).
Eventually they solved both the problems (I have to say they even did it
quick) but you can imagine the atmosphere when the final customer had to
be told that they had invested an overall amount of 25k$ a day for 2
years just not to be able to print a common report and that Oracle just
answered yes, it's a known bug - bug precedence level: low.
Which actually meant: go ** yourselves, we ain't got no time for your
stupid customers. If only they could switch engine... But they switched
to their lawyers in instead and kept the engine running, because no ANSI
was there (and because we all knew that no better stability was to be
found on other vendors anyway).
Some things in escaping the ANSI standard are useful, though. Things like
Oracle's DECODE and the LIMIT clause do make query sets smaller and
quicker.
And yet, IMHO most of the opposition to ANSI comes from a mere
commercial point of view.
This way vendors can keep releasing poor alpha stuff and call it a
stable release without having to worry about spending test money. Test
is something you are going to do yourself, paying for it with your own
money and your own professional credibility. No wonder vendors are happy
with it.
ÐÏËÁ
áÌØÂÅÒÔÏ
ëÉÅ×
--
-_=}{=_-@-_=}{=_--_=}{=_-@-_=}{=_--_=}{=_-@-_=}{=_--_=}{=_-
LoRd, CaN yOu HeAr Me, LiKe I'm HeArInG yOu?
lOrD i'M sHiNiNg...
YoU kNoW I AlMoSt LoSt My MiNd, BuT nOw I'm HoMe AnD fReE
tHe TeSt, YeS iT iS
ThE tEsT, yEs It Is
tHe TeSt, YeS iT iS
ThE tEsT, yEs It Is...
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] ' and in sql query
Hello Guys how can escape chars like ' and so that MySQL doesn't report me errors all the time, for example when i try to add data like It's, i always have errors Thank You -- Best regards, Daniel mailto:[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] rewriting this SQL query to remove subselect
You could try a two step query: $querywords="CREATE TABLE temptable SELECT ... "; $sel_querywords=mysql_query($querywords); $search = "SELECT count(search_table.word) as score, search_table.qid,page_data.contents FROM search_table,page_data,temptable WHERE page_data.pID = search_table.qid AND search_table.word = temptable.word GROUP BY search_table.qid ORDER BY score DESC"; then drop temptable . HTH Sorin Ifrim Scott Mebberson [EMAIL PROTECTED] wrote in message 967ab7$t1$[EMAIL PROTECTED]">news:967ab7$t1$[EMAIL PROTECTED]... Hi Guys, I understand that as of version 3.24, MySQL will support subselects. But untill then (because 3.23 is the latest stable release) I need to convert this SQL satement into something that can be used with v3.2? - the version just before the latest release. $search = "SELECT count(search_table.word) as score, search_table.qid,page_data.contents FROM search_table,page_data WHERE page_data.pID = search_table.qid AND search_table.word IN($querywords) GROUP BY search_table.qid ORDER BY score DESC"; Does anybody have any ideas, thanks for this guys. Any help is much appreciated. -- 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]
