Jump to content


Photo

mySQL annoyance


  • Please log in to reply
15 replies to this topic

#1 stephenk

stephenk
  • Members
  • PipPipPip
  • Advanced Member
  • 62 posts

Posted 21 September 2005 - 08:02 PM

I am developing a news system for a website. I am about to finish it off, with code to edit the news. I have the following code.

$tester = "blah blah blah";
echo $tester ."<br>" .$title ."<br>";

$sql = "update tblnews set title=\"$title\" where count=" .$n2e;

The news title and body is loaded into a form for editing. $title is coming in POST data from the submit page.

$n2e is the unique code for each piece of news

The echo statement returns the values exactly as I want them to be:

blah blah blah
news title

However, when I comment out the echo code, the script seems to enter a blank title, even though the variable name from the echo code is exactly the same as that inside the query. I am not yet working on the code for the body text.

If I change the query to enter $tester, then it enters "blah blah blah" perfectly

I am so confused and any help would be great!

Stephen

#2 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 21 September 2005 - 08:49 PM

have you tried to use mysql_error() to debug the query? I think your server has magic quote turned on, which will add backslashes to any quote marks present in the submitted text. try stripslashes() before putting the results into the query.
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#3 stephenk

stephenk
  • Members
  • PipPipPip
  • Advanced Member
  • 62 posts

Posted 21 September 2005 - 09:09 PM

have you tried to use mysql_error() to debug the query? I think your server has magic quote turned on, which will add backslashes to any quote marks present in the submitted text. try stripslashes() before putting the results into the query.

View Post



Hi,

There doesnt seem to be any error coming from the mysql query. I have this to test everything went OK:

$go = mysql_query($sql, $conn) or die ("could not execute query to update");
if($go) {echo "news article edited successfully";}

... and there are no slashes in the submitted test .. this is just simple "test2" stuff at the minute :)

Thanks for your reply but Im afraid it's still the same!
Stephen

#4 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 21 September 2005 - 09:13 PM

EDITED *NVM* stupid question
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#5 stephenk

stephenk
  • Members
  • PipPipPip
  • Advanced Member
  • 62 posts

Posted 25 September 2005 - 08:32 PM

EDITED *NVM* stupid question

View Post


Anyone have a solution to this? I have come accross another project and have the exact same problem. I am able to echo the query, and put it into phpmyadmin, but yet when I try to run it through PHP it just inserts blank values

Stephen

#6 neylitalo

neylitalo
  • Staff Alumni
  • Advanced Member
  • 1,854 posts
  • LocationMichigan, USA

Posted 25 September 2005 - 09:09 PM

moving to MySQL forum...
http://nealylitalo.net - My personal website, and home of The Netizen's Journal.

#7 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 25 September 2005 - 09:38 PM

try single quotes in the query?
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#8 stephenk

stephenk
  • Members
  • PipPipPip
  • Advanced Member
  • 62 posts

Posted 26 September 2005 - 03:03 PM

Single qoutes returns an error. The current code in my page is as follows:

<?php

$id = $_GET['id'];
$score1 = $_POST['score1'];
$score2 = $_POST['score2'];


include "sqlconn.php";

$sql = "INSERT INTO tbldiv1 (ID, score1, score2) VALUES ($id, '$score1', '$score2') ON DUPLICATE KEY UPDATE score1 = '$score1', score2 = '$score2'";

$sql = $sql;

$go = mysql_query($sql, $conn) or die ("could not execute query 1 to update");

if($go) {echo "Result recorded";}



?>

Using the UPDATE idea does the same thing - everything seems fine but the result entered is blank .. eventhough when I echo the query and put it into PHPMyAdmin its all good.
So simple but it just wont work!...

Stephen

#9 kenrbnsn

kenrbnsn
  • Staff Alumni
  • Advanced Member
  • 8,235 posts
  • LocationHillsborough, NJ, USA

Posted 27 September 2005 - 01:32 AM

Instead of:
[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--][span style=\"color:#0000BB\"]<?php
$sql [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]$sql[/span][span style=\"color:#007700\"];

[/span][span style=\"color:#0000BB\"]$go [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]mysql_query[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$sql[/span][span style=\"color:#007700\"], [/span][span style=\"color:#0000BB\"]$conn[/span][span style=\"color:#007700\"]) or die ([/span][span style=\"color:#DD0000\"]\"could not execute query 1 to update\"[/span][span style=\"color:#007700\"]);

if([/span][span style=\"color:#0000BB\"]$go[/span][span style=\"color:#007700\"]) {echo [/span][span style=\"color:#DD0000\"]\"Result recorded\"[/span][span style=\"color:#007700\"];}
[/span][span style=\"color:#0000BB\"]?>[/span]
[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]
try
[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--][span style=\"color:#0000BB\"]<?php
[/span][span style=\"color:#007700\"]echo [/span][span style=\"color:#DD0000\"]\'Will now attempt to perform <span style=\"font-weight:bold\">\' [/span][span style=\"color:#007700\"]. [/span][span style=\"color:#0000BB\"]$sql [/span][span style=\"color:#007700\"]. [/span][span style=\"color:#DD0000\"]\'</span><br>\'[/span][span style=\"color:#007700\"];

[/span][span style=\"color:#0000BB\"]$go [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]mysql_query[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$sql[/span][span style=\"color:#007700\"], [/span][span style=\"color:#0000BB\"]$conn[/span][span style=\"color:#007700\"]) or die ([/span][span style=\"color:#DD0000\"]\"could not execute query 1 to update\"[/span][span style=\"color:#007700\"]);

if([/span][span style=\"color:#0000BB\"]mysql_affected_rows[/span][span style=\"color:#007700\"]() > [/span][span style=\"color:#0000BB\"]0[/span][span style=\"color:#007700\"]) echo [/span][span style=\"color:#DD0000\"]\"Result recorded\"[/span][span style=\"color:#007700\"];
else echo [/span][span style=\"color:#DD0000\"]\'There was a problem with the query\'[/span][span style=\"color:#007700\"];
[/span][span style=\"color:#0000BB\"]?>[/span]
[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]
Or you could do
[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--][span style=\"color:#0000BB\"]<?php
[/span][span style=\"color:#007700\"]echo [/span][span style=\"color:#0000BB\"]mysql_info[/span][span style=\"color:#007700\"]();
[/span][span style=\"color:#0000BB\"]?>[/span]
[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]
instead of the if at the end.

See if you get any more relavent information from these statements.

Ken


#10 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 27 September 2005 - 04:56 AM

what version of MySQL are you running on? The ON DUPLICATE KEY syntax is not available until 4.1.0

the same thing worked in phpMyAdmin? Are you sure you're copying the query from the view source, not the browser output? You know browsers parse out the tags so you get clean text when you copy.
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#11 stephenk

stephenk
  • Members
  • PipPipPip
  • Advanced Member
  • 62 posts

Posted 27 September 2005 - 04:26 PM

Hi,

Thaks for your feedback.

@kenrbnsn:

I tried your debugging code but it returned all true - eventhough nothing was actually inserted it returned "Result recorded".

@ryanlwh:

I have mySQL 4.1.10. I also did try the UPDATE tbldiv1 SET ... and the output was the same.
I did try copying from source but that also worked perfectly from inside PHPMyAdmin.

----

When I take the query from the view:source and put it into the script, then everything is perfect - could the variables lose their values somehow while the query is going on - and is there any way I could test for this?

Thanks for your continued suggestions and help!
Stephen

#12 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 29 September 2005 - 07:05 PM

hmm that's really weird. you said you've tried echoing the query to screen and it looks fine, but when mysql_query is executed, empty values were added... one thing, are you sure the field name for id is upper case? that could be the problem.
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#13 tharagleb

tharagleb
  • Members
  • Pip
  • Newbie
  • 4 posts

Posted 29 September 2005 - 08:10 PM

Just for grins try this:

$sql = "INSERT INTO tbldiv1 (ID, score1, score2) VALUES (99, '10', '11') ON DUPLICATE KEY UPDATE score1 = '12', score2 = '13'";

This will tell you whether the problem is your variables or not.

Also your update should have added "WHERE ID = '$id'" to it. (I *think*)

#14 stephenk

stephenk
  • Members
  • PipPipPip
  • Advanced Member
  • 62 posts

Posted 30 September 2005 - 10:05 PM

Yeah I tried doing that before and it worked perfectly, that is what made me think it might be the variables .. but I dont know how I can make the page any simpler :S

Stephen

#15 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 30 September 2005 - 10:52 PM

PHP5? With superglobals turned off?
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#16 stephenk

stephenk
  • Members
  • PipPipPip
  • Advanced Member
  • 62 posts

Posted 02 October 2005 - 08:56 PM

Its PHP5 yeah, didnt see anything about superglobals in php.ini
The script behaves the same way when uploaded so Im at a total loss now :(

Maybe someone will come up with the solution around here though :)

Cheers
Stephen




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users