PHP MySQLi Query returning null with valid syntax

Question

Ok so,

I have been trying to make a ticket system for my wxPanel in order to provide basic support for the application. Although I am easily able to make a database record with the provided code:

PHP:

if (isset($_POST['submit'])) {
    $subject = $_POST['subject'];
    $message = $_POST['message'];

    $date = date('D M H:i');

    $subject = mysqli_real_escape_string($subject);
    $message = mysqli_real_escape_string($message);
    $ticket_id = 'TICK_'.rand(00000,99999);

    if (strlen($subject) === 0) {
        echo "Subject Invalid.";
    } elseif (strlen($message) === 0) {
        echo "Message Invalid.";
    } else {
        mysqli_query("INSERT INTO tickets VALUES(
        NULL,
        '".$ticket_id."',
        '".$_SESSION['user']."',
        '".$subject."',
        '1',
        '".$date."',
        '".$message."'
        )");
    }
    header('Location: /view-ticket?identifier='.$ticket_id);
}

Works fine... Then there is this, which is ment to fetch the ticket records and display the titles one by one:

PHP:

$query = mysqli_query("SELECT `subject`,`ticket_id` FROM tickets WHERE `username` = '".$_SESSION['user']."'");

while ($row = mysqli_fetch_assoc($query)) {
    $tickets = $row['subject'];
    $id = $row['ticket_id'];
}

foreach ($tickets as $ticket) {
    echo '
    <a href="view-ticket?identifier='.$id.'"><h2>'.$ticket.'</h2></a>
    ';
}

This always returns NULL. And also none of this works either:

if (isset($_GET['identifier']) === false || empty($_GET['identifier']) === true) {
    header('Location: /tickets');
    exit();
}

$id = mysqli_real_escape_string($_GET['identifier']);

$query = mysqli_query("SELECT `ticket_id`,`message`,`timestamp`,`status` FROM tickets WHERE `ticket_id` = '".$id."'");

while($row = mysqli_fetch_assoc($query)) {
    $ticket_id = $row['ticket_id'];
    $message = $row['message'];
    $timestamp = $row['timestamp'];
    $status = $row['status'];
}

foreach($message as $msg) {
    echo '
    <div class="ticket-message">
        <h2>'.$message.'</h2>
    </div>';
}

Thank you in advance!

p.s. Some of my code may be messy. Advice is always appreciated :)

For one thing, mysqli_real_escape_string() needs a DB connection parameter $subject = mysqli_real_escape_string($subject); which should read as $subject = mysqli_real_escape_string($con,$subject); - $con being the DB connection variable. Plus, I don't know how you're connecting with the rest of your code. This $query = mysqli_query("SELECT should look something like $query = mysqli_query($con,"SELECT... - Your DB connection wouldn't happen to be mysql_ by any chance, would it? — Funk Forty Niner
– Funk Forty Niner, Commented May 28, 2014 at 22:55
I am using the mysqli_connect, I wasn't aware that I needed to use a connection variable to directly interact with the database. — user3685539
– user3685539, Commented May 28, 2014 at 23:08
Yep and for so many reasons using mysqli_* functions. In the olden days of mysql_, once a connection was established, the functions didn't need it afterwards; all the more reasons for security reasons. So, doing that may very well fix all or most of the problems. — Funk Forty Niner
– Funk Forty Niner, Commented May 28, 2014 at 23:09
Add error reporting to the top of your file(s) error_reporting(E_ALL); ini_set('display_errors', 1); mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); if you're not already doing so during development. It will signal any errors found, if any. — Funk Forty Niner
– Funk Forty Niner, Commented May 28, 2014 at 23:14
It is also very dangerous to code INSERT's without naming the columns as well as providing the data. Some bright spark is bound to look at the table definition and decide to move column2 after columns4 and then at best your query breaks and at worst puts data into the wrong column which nobody realises for days or weeks and really messes up your database. — RiggsFolly
– RiggsFolly, Commented May 28, 2014 at 23:26

RiggsFolly · Accepted Answer · 2014-05-29 01:02:43Z

2

Once you get the SELECT query working you are also going to have to look at the code that processes the results.

If ticket_id identifies a unique row

$query = mysqli_query($con, "SELECT ticket_id,message,timestamp,status 
                             FROM tickets WHERE ticket_id = '$id'");


$row = mysqli_fetch_assoc($query);
$ticket_id = $row['ticket_id'];
$message   = $row['message'];
$timestamp = $row['timestamp'];
$status    = $row['status'];

echo '<div class="ticket-message"><h2>'.$message.'</h2></div>';

If ticket_id does not identify a unique row

$query = mysqli_query($con, "SELECT ticket_id,message,timestamp,status 
                             FROM tickets WHERE ticket_id = '$id'");

// initialise the arrays that hold multiple row results
$ticket_id[] = array();
$message[]   = array();
$timestamp[] = array();
$status[]    = array();

while($row = mysqli_fetch_assoc($query)) {
   $ticket_id[] = $row['ticket_id'];
   $message[]   = $row['message'];
   $timestamp[] = $row['timestamp'];
   $status[]    = $row['status'];
}

foreach($message as $msg) {
   echo '<div class="ticket-message"><h2>'.$msg.'</h2></div>';
}

edited May 29, 2014 at 1:02

answered May 28, 2014 at 23:45

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

user3685539 Over a year ago

Thank you both of you for helping, everything is up and running! Enjoy the rest of your evenings.

user3685539 Over a year ago

Ok, ran into one small problem

user3685539 Over a year ago

I have found that if I try to output $username or $timestamp it returns "Array".

RiggsFolly Over a year ago

I dont see any reference to $username but $timestamp is an array if you are using my amended code. It has to be as it contains data from multiple fetches

RiggsFolly Over a year ago

Well actually I may have assumed to much. As your query has a WHERE Ticket_id = '$id', if that identifies a unique row in the table then the code can be changed, give me a second to change my answer.

|

Collectives™ on Stack Overflow

PHP MySQLi Query returning null with valid syntax

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related