mySQL annoyance

[stephenk]

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

[ryanlwh]

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.

[stephenk]

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.

297896[/snapback]

 

 

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

[ryanlwh]

EDITED *NVM* stupid question

[stephenk]

EDITED *NVM* stupid question

297907[/snapback]

 

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

[neylitalo]

moving to MySQL forum...

[ryanlwh]

try single quotes in the query?

[stephenk]

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

[kenrbnsn]

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

 

[ryanlwh]

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.

[stephenk]

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

[ryanlwh]

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.

[tharagleb]

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*)

[stephenk]

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

[ryanlwh]

PHP5? With superglobals turned off?

[stephenk]

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