One Query

PHP
1

I’m trying to use one query instead of two. However, if I use the following code the first chapter doesn’t appear. If I strip $row = mysql_fetch_assoc($sql); then it appears. Of course, then the ml_title doesn’t show.

<?php
	require_once('conn.php');
	
	$sql = mysql_query("
	SELECT * 
	FROM 
		movies_movies AS M, 
		movies_chapters AS C, 
		movies_lang AS L 
	WHERE 
		M.id = C.mc_mov_id 
	AND 
		M.id = L.ml_mov_id
	AND 
		M.id = 1 
	ORDER BY 
		C.mc_chapter_num ASC
	") or die(mysql_error());
	
	$row = mysql_fetch_assoc($sql);
	
	echo '<h1>'.$row['ml_title'].'</h1>';
	
	while($title = mysql_fetch_assoc($sql)) {
		echo $title['mc_chapter_num'].' '.$title['mc_chapter_title'].'<br />';
	}
?>
2

There a couple of ways to deal with this. You can reset the row pointer in the result set back to the beginning before your loop, so that it fetches the first row again.

<?php 
    require_once('conn.php'); 
     
    $result = mysql_query(" 
    SELECT * 
    FROM 
        movies_movies AS M, 
        movies_chapters AS C, 
        movies_lang AS L 
    WHERE 
        M.id = C.mc_mov_id 
    AND 
        M.id = L.ml_mov_id 
    AND 
        M.id = 1 
    ORDER BY 
        C.mc_chapter_num ASC 
    ") or die(mysql_error()); 
     
    $row = mysql_fetch_assoc($result); 
     
    echo '<h1>'.$row['ml_title'].'</h1>'; 
     
    mysql_data_seek($result, 0);

    while($title = mysql_fetch_assoc($result)) { 
        echo $title['mc_chapter_num'].' '.$title['mc_chapter_title'].'<br />'; 
    } 
?>

Or you could move your heading to within the loop, and use a conditional to only print it once.

<?php 
    require_once('conn.php'); 
     
    $result = mysql_query(" 
    SELECT * 
    FROM 
        movies_movies AS M, 
        movies_chapters AS C, 
        movies_lang AS L 
    WHERE 
        M.id = C.mc_mov_id 
    AND 
        M.id = L.ml_mov_id 
    AND 
        M.id = 1 
    ORDER BY 
        C.mc_chapter_num ASC 
    ") or die(mysql_error()); 
     
    while($title = mysql_fetch_assoc($result)) { 
        if (!isset($printed_title)) {
            echo '<h1>'.$row['ml_title'].'</h1>'; 
            $printed_title = true;
        }

        echo $title['mc_chapter_num'].' '.$title['mc_chapter_title'].'<br />'; 
    } 
?>
3

Resetting the row pointer works perfectly, as I have much more to display than just the title. Excellent!

Thanks! :slight_smile: