SQL query not working anymore

markusdavid · June 29th, 2008, 01:59 PM

Dear all,
Was wondering if someone could help. I have recently transferred to a new server that runs an upgraded version on sql. My previous sql was hosted on Cpanel:
Server version: 5.0.51a-community
MySQL client version: 5.0.51a

My new server is hosted by Heart Internet's extend:
Server version: 4.1.22-standard
MySQL client version: 4.1.22

With a few tweaks I managed to get my site working on the new server, all apart from one query and I cant solve it. I have also had another coder try and he counldnt either! I have a copy of the site still running on the old server so that you can see the difference. Could anyone help?

More info: Its an estate agents website, and the query is retreving information from a search.

Marc · June 29th, 2008, 02:14 PM

Do you have the SQL for the query? Can you show us?

markusdavid · June 29th, 2008, 02:28 PM

Thank you for replying marc. Literally the guy who was working on it has just solved it! I have no idea how but its working! I really appreciate you replying though.

Marc · June 29th, 2008, 02:33 PM

No problem mate. Just for future reference of different members, what was the solution (if you can share of course!)

Cheers and glad you got it sorted!

markusdavid · June 29th, 2008, 02:41 PM

Hi marc, would loved to have shared, but I have no idea what they guy has done! He done stuff to the database and my query and other stuff in my sql/php. I wouldnt know where to begin! Sorry about that.

DeMan · July 14th, 2008, 05:56 PM

Hi all,
I don't know the difference between the old mysql Server version: 4.1.22-standard and the new Server version: 5.0.51a-community, but I've noticed a few things which I would like to share with you.

It seems that some queries run on the old server don't work on the new version. I think the easiest way to explain this is the difference between HTML and XHTML. The new Server version: 5.0.51a-community is more complex and requires more detailed and correct information.

The new Server version: 5.0.51a-community doesn't 'think'

you have to tell it everything.

In markusdavids case it didn't know where to get the information from, although it was obvious for the old version and for a coder

i.e: a form action = post ... the old version would automatically get the information from the form, for the new version you have to specify $client = $_POST['client']

Do I make sense???

I'm having a problem with the new version myself at the moment and I'm not sure how to do it.

Why did they make it so difficult for us?????

Thanks for reading my waffle

DeMan

stu2000 · July 14th, 2008, 07:19 PM

that sounds like a difference in the register globals setting, that's a PHP setting, not a MySQL one... or I've got completely the wrong end of the stick lol

Stu

DeMan · July 15th, 2008, 08:16 AM

Ok, I've got a question then

Why does a query work on the old version but not on the new one?

what are the main differences in coding php for the new Mysql version?

I've got one at the moment and I'm struggeling

does anyone know?????

Thanks
DeMan

stu2000 · July 22nd, 2008, 06:59 AM

what is the exact query you are running ok on the old one, and that doesn't work on the new version?

Do you have an error message at all?

DeMan · August 4th, 2008, 01:38 PM

Hiya,
this is the code, I got it from widgetmonkey.com.
Its a exam question script (questions and answers are stored in the database)
It works perfectly fine on the old version of the mysql Server.

I don't get an error message, but the query doesn't run. Instead it goes back to the beginning (showing the questions and answers) No matter what I do, I can't get it to work.

It seems like a little problem, but I'm drawing a blank.

Any help would be greatly appreciated!!

Code:


			
<?php
//copyright widgetmonkey.com 2001
include("contentdb.php");
$display = mysql_query("SELECT * FROM $table ORDER BY id",$db);
if (!$submit) {
    echo "<form method=post action=$PHP_SELF>";
    echo "<table border=0>";
    while ($row = mysql_fetch_array($display)) {
    $id = $row["id"];
    $question = $row["question"];
    $opt1 = $row["opt1"];
    $opt2 = $row["opt2"];
    $opt3 = $row["opt3"];
    $answer = $row["answer"];
    echo "<tr><td colspan=3><br><b>$question</b></td></tr>";
    echo "<tr><td>$opt1 <input type=radio name=q$id value=\"$opt1\"></td><td>$opt2 <input type=radio name=q$id value=\"$opt2\"></td><td>$opt3 <input type=radio name=q$id value=\"$opt3\"></td></tr>";
    }
    echo "</table>";
    echo "<input type='submit' value='See how you did' name='submit'>";
    echo "</form>";
}
elseif ($submit) 
{
    $score = 0;
    $total = mysql_num_rows($display);
        while ($result = mysql_fetch_array($display)) 
        {
            $answer = $result["answer"];
            $q = $result["q"]; 
        if ($$q == $answer) 
        {
        $score++; 
        }
    }
    echo "<p align=center><b>You scored $score out of $total</b></p>";
    echo "<p>";
    if   ($score == $total) {
    echo "Congratulations! You got every question right!";
    }
    elseif ($score/$total < 0.34) {
    echo "Oh dear. Not the best score, but don't worry, it's only a quiz.";
    }
    elseif ($score/$total > 0.67) {
    echo "Well done! You certainly know your stuff.";
    }
    else {
    echo "Not bad - but there were a few that caught you out!";
    }
echo "</p>";
echo "<p>Here are the answers:";
echo "<table border=0>";
$display = mysql_query("SELECT * FROM $table ORDER BY id",$db);
while ($row = mysql_fetch_array($display)) {
$question = $row["question"];
$answer = $row["answer"];
$q = $row["q"];
echo "<tr><td><br>$question</td></tr>";
if ($$q == $answer) 
        {
        echo "<tr><td>&raquo;you answered ${$q}, which is correct</td></tr>";
        }
elseif ($$q == "") {
echo "<tr><td>&raquo;you didn't select an answer. The answer is $answer</td></tr>";
}
else {
echo "<tr><td>&raquo;you answered ${$q}. The answer is $answer</td></tr>";
}
}
echo "</table></p>";
}
?>

CloudedVision · August 7th, 2008, 07:58 PM

Would you have happened to upgrade PHP as well? There are a few problems in the script that could happen depending on your PHP config.

DeMan · August 9th, 2008, 05:39 AM

Yep, PHP got upgraded from 2.11.0 to 2.11.4

I got the script so far that it actually goes to the page with the answers, but the answers aren't displayed. It seems that its not quering the database.

Have you got any clues as to whats wrong with the code?

CloudedVision · August 9th, 2008, 12:47 PM

Could you paste what the code outputs?

DeMan · August 9th, 2008, 06:14 PM

It queries the database and pulls out the correct answers, but doesn't compare the input answer with the correct one.

Code:


			
<form method=post action=>
<table border=0><tr><td colspan=3><br><b>test question</b></td></tr>
<tr><td>test answer <input type=radio name=q3 value="test answer"></td>
<td>answer test <input type=radio name=q3 value="answer test"></td>
<td>test test <input type=radio name=q3 value="test test"></td></tr>
<tr><td colspan=3><br><b>when are we going?</b></td></tr>
<tr><td>i dont know <input type=radio name=q4 value="i dont know"></td>
<td>soon, maybe <input type=radio name=q4 value="soon, maybe"></td>
<td>or whenever <input type=radio name=q4 value="or whenever"></td></tr></table>
<input type='submit' value='See how you did' name='submit'></form>

I select an answer and hit 'submit' and this is what i get, it always says "you didn't select an answer".

Code:


			
<p align=center>
<b>You scored 0 out of 2</b></p>
<p>Oh dear. Not the best score, but don't worry, it's only a quiz.</p>
<p>Here are the answers:<table border=0><tr><td>&raquo;you didn't select an answer. The answer is answer test</td></tr>
<tr><td>&raquo;you didn't select an answer. The answer is or whenever</td></tr></table></p>

DeMan · August 14th, 2008, 08:12 AM

Has anyone got any ideas?
I just need some tips as to what has changed with the new php.

thanks heaps

CloudedVision · August 14th, 2008, 05:05 PM

Do some debugging. Put some echo's throughout the script telling you what its doing and outputting the variables.