Member Avatar for Sophia_1

Hi everyone, tried to display next and previous records from the coding below but unable to do so.It suppose to display next records for the current user with the when user click "Next" but currently it doesn't display "Next" but only display "Random". When i clicked "Random", it displays next records for all users. The following message also is displayed ie "Champ: 'picid' dans field list est ambiguRandom". The same goes to "Previous". Please advise.
Thanks.

            <?php
            error_reporting(E_ALL ^ E_NOTICE);
            $host = 'localhost';  
            $user = 'user';  
            $pass = '';  
            $dbname = 'pq';  
            $con = mysqli_connect($host, $user, $pass,$dbname);  
            if(!$con){  
              die('Could not connect: '.mysqli_connect_error());  
            }
                $_SESSION['userid']; // it will print the userid value

            $currentId = mysqli_real_escape_string($con,$_GET['picid']);
            if($q = mysqli_query($con, 'SELECT *,
            (SELECT IFNULL(max(picid),-1) FROM general,progress WHERE userid='.$_SESSION['userid'].' and  `general.picid` < '.$currentId.') as previousid,
            (SELECT IFNULL(min(picid),-1) FROM general,progress WHERE userid='.$_SESSION['userid'].' and `general.picid` > '.$currentId.') as nextid
              FROM general,progress WHERE `general.picid` = ' . $currentId)){
                if($row = mysqli_fetch_array($q, MYSQL_BOTH)){

                 echo '<div id="picid">picid' . $row['picid'] . '</div>';
                 echo '<div id="userid">userid ' . $row['userid'] . '</div>';
                echo '<div id="progress">progress ' . $row['progress'] . '</div>'; 
                echo '<div id="Rating">Rating ' . $row['Rating'] . '</div>';
                    if ($row['previousid'] > -1){
                        echo '<a href="progress.php?picid='.$row['previousid'].'"class="random">Previous</a>';
                    }
                    if ($row['nextid'] > -1){
                        echo '<a href="progress.php?picid='.$row['nextid'].'"class="random">Next</a>';
                    }
                } else {
                    echo 'Not found';
                }
            } else {
                echo mysqli_error($con);
            }
            echo "</div>"; 
            $query = "select * from general,progress order by RAND() LIMIT 1";
            $result = mysqli_query($con, $query) or die("Query failed: " . mysqli_errno($con));
            while ($row = mysqli_fetch_array($result, MYSQL_BOTH)){
                echo '<a href="ipdprogress.php?picid='.$row['picid'].'"class="random">Random</a>';
            }
            mysqli_close($con); 
            ?>
  • 2 Contributors
  • 2 Replies
  • 416 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Sophia_1

Dani AI

Generated

Short diagnosis for (and thanks for the pagination hint): the SQL is ambiguous and your subqueries aren’t restricting to the current user; that’s why the “Next”/“Previous” come from the wrong set and you see the ambiguous picid error. Fixes: always qualify columns (use aliases), join the tables explicitly, and either (A) use user-scoped subqueries or (B) run small LIMIT queries for prev/next — both are shown below.

A: Single-row query that returns previous/next using joined, qualified subqueries (use prepared statements and bind the userid and picid):

SELECT g.*, p.progress, p.rating,
  (SELECT MAX(g2.picid)
   FROM general g2 JOIN progress p2 ON p2.picid = g2.picid
   WHERE g2.userid = ? AND g2.picid < g.picid) AS previousid,
  (SELECT MIN(g2.picid)
   FROM general g2 JOIN progress p2 ON p2.picid = g2.picid
   WHERE g2.userid = ? AND g2.picid > g.picid) AS nextid
FROM general g
LEFT JOIN progress p ON p.picid = g.picid
WHERE g.picid = ?

B: Simpler and often faster — two tiny queries that return the single previous and next id:

SELECT g.picid FROM general g JOIN progress p ON p.picid=g.picid
 WHERE g.userid=? AND g.picid < ? ORDER BY g.picid DESC LIMIT 1;

SELECT g.picid FROM general g JOIN progress p ON p.picid=g.picid
 WHERE g.userid=? AND g.picid > ? ORDER BY g.picid ASC  LIMIT 1;

C: If you prefer PHP-side logic, fetch the ordered picid list for the user, put them in an array, find the current index and pick neighbors (good for small per-user sets).

Troubleshooting notes: call session_start() and validate/escape input (or better, use prepared statements). Decide INNER vs LEFT JOIN depending on whether you want to skip pics without a progress row. Check your link targets (progress.php vs ipdprogress.php) and CSS class names — mismatches can make it look like links are broken. Index general(userid,picid) for performance.

Recommended Answers

All 2 Replies

Member Avatar for jkon

It seems that what you want is a simple pagination. Is this the case?

Of course you can get the previous or next record of a record received by a sql query but this is a bit more complicated and probably has nothing to do with what you mean.

Member Avatar for Sophia_1

Hi everyone, managed to get the next and previous records with the coding below but the next and previous records are from progress table only. Please advise how to get the next and previous records from 2 tables ie general table and progress table. Fyi, the fields for general (userid,picid) and progress (picid,progress,rating). Thanks a lot.

            <?php
            error_reporting(E_ALL ^ E_NOTICE);
            $host = 'localhost';  
            $user = 'user';  
            $pass = '';  
            $dbname = 'pq';  
            $con = mysqli_connect($host, $user, $pass,$dbname);  
            if(!$con){  
              die('Could not connect: '.mysqli_connect_error());  
            }
                $_SESSION['userid']; // it will print the userid value

            $currentId = mysqli_real_escape_string($con,$_GET['picid']);
            if($q = mysqli_query($con, 'SELECT *,
            (SELECT IFNULL(max(picid),-1) FROM progress WHERE `picid` < '.$currentId.') as previousid,
            (SELECT IFNULL(min(picid),-1) FROM progress WHERE  `picid` > '.$currentId.') as nextid 
              FROM progress WHERE `picid` = ' . $currentId)){
                if($row = mysqli_fetch_array($q, MYSQL_BOTH)){   
                 $picid=$row['picid'];
                 $userid=$row['userid'];
                $progress=$row['progress'];     
                 $rating=$row['rating'];
                    if ($row['previousid'] > -1){
                      echo '<a href="progress.php?picid='.$row['previousid'].'"class="random">Previous</a>';
                            }
                    if ($row['nextid'] > -1){
                       echo '<a href="progress.php?picid='.$row['nextid'].'"class="random">Next</a>';
                       }
                } else {
                    echo 'Not found';
                }
            } else {
                echo mysqli_error($con);
            }
            echo "</div>"; 
            mysqli_close($con); 
            ?>
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.