How to pull one row at a time from the database using php and mysql

Question

I am creating a survey that asks the user questions in order to determine what Majors would be most appropriate for that user based on their answers. I want to display the questions one at a time. The user should be able to press the next button and the next question stored in the database will appear on the screen. Right now I have my code working to where it displays the first question from the database, but I still need to get the next button working. I need an efficient way to do this since I have 60 questions stored in the database. Any help is greatly appreciated! Below is a picture of how my survey currently looks!

Survey Link

Below is my code!

$dbhost = "localhost";
$dbuser = "root";
$dbpass = "Cherries7";

$conn = mysql_connect($dbhost, $dbuser, $dbpass);

if(! $conn )
{
        die('Could not connect: ' . mysql_error());
}

$id = 1;        //global variable that represents id

$sql = "SELECT questiontext FROM md WHERE ID= '$id'" ;
mysql_select_db('MajorDecider');
$retval = mysql_query( $sql, $conn );
$nr = mysql_num_rows($retval);

if(! $retval ) {
        die('Could not get data: ' . mysql_error());
}

else if($nr == 0) {
    echo "<h2>Question not found.</h2>";
}

//there should only be one question with specified id number
else if($nr == 1) {
    $row = mysql_fetch_array($retval);
    echo " <form  method='POST'>
    <section id='question'><table id='questions'> 
    <tr> 
        <td> {$row['questiontext']} </td> 
        <td> <input type='radio' name='choose' id='interested' value='true' /> Yes </td> 
        <td> <input type='radio' name='choose' id='uninterested' value='false' /> No </td>          
        <td> <button name='next'>Next</button> </td> 
    </tr> 
    </table></section></form> ";
}

This is a really broad question. There are dozens of ways to do it. Which methods have you tried and where did you get stuck? — Matt, Mar 24 '17 at 19:10
one of yours method you can use script.php?step=1 and just change number for other steps, when u click NEXT button put next number 2, 3, 4, ............ — Mario, Mar 24 '17 at 19:12
If you're writing new code, **_please_ don't use the `mysql_*` functions**. They are old and broken, were deprecated in PHP 5.5 (which is so old it no longer even receives security updates), and completely removed in PHP 7. Use [`PDO`](https://secure.php.net/manual/en/book.pdo.php) or [`mysqli_*`](https://secure.php.net/manual/en/book.mysqli.php) with _prepared statements_ and _parameter binding_ instead. See http://stackoverflow.com/q/12859942/354577 for details. — ChrisGPT was on strike, Mar 24 '17 at 19:15
I would recommend you doing the logic (retrieve/next step/back step/send) in javascript/jquery where you fetch all questions at once via ajax. That means only one request, one query and you visitor wont keep being redirected to the next page (unsexy in 2017 imo). — Yolo, Mar 24 '17 at 19:18

score 1 · Answer 1 · edited May 23 '17 at 12:17

1

Add a hidden input inside your form that holds the row ID.

<input type='hidden' name='id' value='{$row['ID']}'>

Then, before you execute your query, check for an ID in $_POST. If there's one there, increment it and use that.

$id = 1;
if (isset($_POST['id'])) {
    $id = $_POST['id'] + 1;
}

You'll need to modify your query a little so that it selects the ID column as well. Also, if you use >= instead of > it will still work in case there are any missing IDs.

$sql = "SELECT ID, questiontext FROM md WHERE ID >= '$id' ORDER BY ID";

Two more things, already mentioned in the comments, but important enough to reiterate. First, you are using the mysql extension, which is no longer supported by PHP. You need to update your code to use either mysqli or PDO. Second, when you use a variable directly in your SQL, (like WHERE ID >= '$id') you are vulnerable to SQL injection. Binding your values to prepared statements will reduce that vulnerability.

edited May 23 '17 at 12:17

Community

1
1

answered Mar 24 '17 at 19:39

Don't Panic

41,125
10
61
80

Sorry for using the msql extension, this is the way we learned it in class. Also, I updated my code with the statements you mentioned. Now the question isn't showing up at all so I don't think this worked. I thought the $_POST would be used for the next button, since I want to display the next question once the user clicks on the next button. – Rixy Portillo Mar 24 '17 at 21:34
The question not showing up at all sounds like the query didn't return anything. Is the ID column numeric? I was assuming it was an integer. – Don't Panic Mar 24 '17 at 22:03
Yes, the ID column is numeric. However it's not just the question that doesn't show up, it's the whole section that holds the question, answers, and the next button that is missing. – Rixy Portillo Mar 25 '17 at 13:55
Oh, I just realized what's causing that. It's the `else if($nr == 1) {` part. After I changed the query to use >= instead of just =, it will return more than one row. It still only just fetches the first one, though. So, either change it back to = or change that `else if` to just an `else`. – Don't Panic Mar 25 '17 at 14:20
That worked in order to get my question displaying on the screen. However, when I press the next button, it is not displaying the next question in the database. Do you think there is anything else wrong with my code or should everything be working if I changed my code accordingly to what you suggested? – Rixy Portillo Mar 27 '17 at 01:15
I found my mistake and now it is working. Thanks so much for the help!! – Rixy Portillo Mar 27 '17 at 16:40
Great! You're welcome. (Sorry I hadn't had time to reply to your last comment yet.) – Don't Panic Mar 27 '17 at 16:41

How to pull one row at a time from the database using php and mysql

1 Answers1