Why does this JOIN query work in shell and not in php?

Question

Cannot seem to find a post addressing this particular issue. The SQL query (as below) works fine when I execute from shell but in php it is throwing an undefined index. I think the problem is that it is not recognising the disambiguation of attributes when in php.

Error I get in php is:

Notice: Undefined index: s.RowNumber in /home/xx/xx/xxx/xxx/checkAvailability.php on line 14.

Code:

<?php
include'connect.php'
?>
    <form method="post" action="chooseDate.php">
    <?php   
    $sql = "SELECT s.RowNumber 
            FROM Seat AS s LEFT JOIN Booking AS b 
            ON s.RowNumber = b.RowNumber 
            AND b.PerfDate = '2016-12-12' 
            AND b.PerfTime = '20:30:00' 
            WHERE b.RowNumber IS NULL";
    $handle = $conn->prepare($sql); 
    $handle->execute(); 
    $res = $handle->fetchAll(); 
        foreach($res as $row) {
            echo "<input name='Seat' type='checkbox' value=".$row['s.RowNumber']."><br>";  
        }   
    ?>
    <input class="mybutton" type="submit" value="Choose Date"/>
    </form>

Can anyone elaborate on the problem? Thanks in advance.

If you get _"Undefined index"_, it's not the SQL-query that's the issue. Read more about it here: http://stackoverflow.com/questions/4261133/php-notice-undefined-variable-and-notice-undefined-index — M. Eriksson, Dec 03 '16 at 10:29

maestroosram · Accepted Answer · 2016-12-03T10:55:43.813

0

You must use $row['RowNumber'] in your foreach loop. s. is just the table alias, you should ignore it.

edited Dec 03 '16 at 10:55

answered Dec 03 '16 at 10:30

maestroosram

196
12

It seems to be `RowNumber`, not `rowNumber`. Otherwise it will most likely throw the same notice again. :) – M. Eriksson Dec 03 '16 at 10:31
Yes, did try that before. It gets rid of the error but returns blank results. – Shooresh Dec 03 '16 at 10:33
Hello, thanks for that but it is not recognising the fetch_object() method : "Fatal error: Uncaught Error: Call to undefined method PDOStatement::fetch_object() in /home/cuc/sg611/public_html/assignment2/checkAvailability.php:12 Stack trace: #0 {main}." – Shooresh Dec 03 '16 at 10:54
@ScheurichGolzari sorry my fault, I thought you were using mysqli. Have you tried to `print_r()` the `$res` variable? Can you paste the result? – maestroosram Dec 03 '16 at 10:58
hah. Well. I just did and sure it gives the result in a way... imagine 600 of these in a continuous string taking the entirety of the page: Array ( [0] => Array ( [RowNumber] => U03 [0] => U03 ) :D – Shooresh Dec 03 '16 at 11:15
If it is as you said echoing `"
"` should work – maestroosram Dec 03 '16 at 12:38

score 0 · Answer 2 · answered Dec 03 '16 at 10:34

0

Try giving an alias to s.RowNumber and use it in the echo statement

answered Dec 03 '16 at 10:34

Quousque tandem abutere

54
8

Why should he use an alias for the column name? He just needs to remove the `s.` (table alias) when fetching the results from his array. – M. Eriksson Dec 03 '16 at 10:34

Why does this JOIN query work in shell and not in php?

2 Answers2