Can't make MySQL queries in PHP webpage

Question

So I'm working on a website where I need to pull data from a MySQL server and show it on a webpage. I wrote a simple PHP script to read data from the database depending upon an argument passed in the URL and it works just fine.

Here is the script:

<?php
function updator($item)
{
  $servername = "localhost";
  $username = "yaddvirus";
  $password = "password";
  $dbname = "database";

  // Create connection
  $conn = mysqli_connect($servername, $username, $password, $dbname);

  // Check connection
  if (!$conn) {
      die("Connection failed: " . mysqli_connect_error());
  }
  echo "Connected successfully";
  $table = "inventory";
  //$item = "Rose Almonds";

  $sql = "SELECT * FROM $table WHERE item = '$item'";
  $result = $conn->query($sql);

  while($data=$result->fetch_assoc()){
    echo "<h1>{$data['item']}</h1><br>";
    echo "<h1>{$data['item_desc']}</h1><br>";
    echo "<h1>{$data['price125']}</h1><br>";
    echo "<h1>{$data['price250']}</h1><br>";
  }
      //echo "0 results";
  $conn->close();
}
if (defined('STDIN')) {
  $item = $argv[1];
} else {
  $item = $_GET['item'];
}
//$item = "Cherry";
updator($item);

?>

This script works exactly as expected. I call it using http://nutsnboltz.com/tester.php?item=itemname and it pulls and shows the data just fine.

P.S You can test it out by using Cherry or Blueberry as items.

The problem is, when I'm trying to put this data in my productpage.php file, I can't get the data to show up. Here's how the file hierarchy goes:

<php
*Exact same php script as above*
?>
<html>
<head>
Header and navbar come here
</head>
<body>
  <div class="container-fluid">
    <div class="row">
      <div class="col-4">
        <h1> RANDOM TEXT BEFORE </h1>
        <?php
        while($data=$result->fetch_assoc()){
          echo "<h1>{$data['item']}</h1><br>";
          echo "<h1>{$data['item_desc']}</h1><br>";
          echo "<h1>{$data['price125']}</h1><br>";
          echo "<h1>{$data['price250']}</h1><br>";
        } 
         ?>

      </div>
      <div class="col-8">
        <H!> MORE RANDOM TEXT</h1>
      </div>

    </div>
</div>
</body>
<footer>
footer here
scripts etc
</footer>
</html>

So the script above the footer prints everything just fine. However, down where the HTML is, nothing is printed after the PHP code. It only shows my Navbar and the H1 tag saying "RANDOM TEXT BEFORE" and that's about it. My footer is gone along with everything else.

What exactly is the issue here and how do I fix this?

Are you sure your opening tag is correct as in the question you have ` — Nigel Ren, Mar 06 '20 at 09:54
I know that `mysqli_connect()` is an alias of `mysqli::__construct()` but the fact that you're using `mysqli` in both procedural (`mysqli_connect`) and OOP style (`$conn->query()`) sets my teeth on edge... that *shouldn't* cause your error though. Your error sounds like a fatal PHP error (it dies so you loose the footer et al) without any error reporting on - [switch on error reporting](https://stackoverflow.com/questions/1053424/how-do-i-get-php-errors-to-display) and see what it says. — CD001, Mar 06 '20 at 09:55
@CD001 I totally get your cringe but at the moment I really just need to get this up and running. Besides, I'm pretty sure that it isn't causing any errors. — YaddyVirus, Mar 06 '20 at 10:01
Yeah but the fact that your footer and everything else below `while($data=$result->fetch_assoc()){` is missing *implies* that there's a fatal error there... it could be something as simple as `$conn->query($sql);` returning `false` - so `$result` is then `false` so `$result->fetch_assoc()` is attempting to call a method on a boolean and dying horribly. You'll need to turn on `display_errors` or check the logs to know what *exactly* is going wrong. — CD001, Mar 06 '20 at 10:05
@CD001 I'm working with a webserver here. Any idea how to turn on display_errors for that? — YaddyVirus, Mar 06 '20 at 10:06
Actually - looking at what you've got here, your `$result` variable is defined with the scope of the `updator` function so doesn't exist when you're attempting to call it globally - so I'm pretty sure the error will be that you're calling `fetch_assoc()` on `null`. Yeah - I put the link into how to display errors on the previous comment ;) Here it is again: https://stackoverflow.com/questions/1053424/how-do-i-get-php-errors-to-display — CD001, Mar 06 '20 at 10:09
@CD001 I tried making the `$result` variable global but no avail. — YaddyVirus, Mar 06 '20 at 10:19
It's getting a bit verbose for comments - I've posted an answer, see if that fixes the issue ;) — CD001, Mar 06 '20 at 10:23

CD001 · Accepted Answer · 2020-03-06T10:31:40.297

0

The problem seems to be that you're declaring $result inside the updator function, so it's not available when you're attempting to call it later.

The best thing to do might be to return $result from the function and assign that to a variable - something like this:

function updator($item)
{
    // ... some code ...

    $sql = "SELECT * FROM $table WHERE item = '$item'";
    $result = $conn->query($sql);

    // ... some more code ...

    return $result;
}

<-- HTML CODE HERE -->

<?php
$item = !empty($_GET['item']) ? $_GET['item'] : false;

// yes I know it's a bit hacky to assign the variable
// within the 'if' condition...
if($item && $result = updator($item)) {
    while($data=$result->fetch_assoc()){
        echo "<h1>{$data['item']}</h1><br>";
        echo "<h1>{$data['item_desc']}</h1><br>";
        echo "<h1>{$data['price125']}</h1><br>";
        echo "<h1>{$data['price250']}</h1><br>";
    } 
}
?>

edited Mar 06 '20 at 10:31

answered Mar 06 '20 at 10:19

CD001

8,332
3
24
28

Hmmm give me a minute - I'll pull your code onto my dev box and see what it says... – CD001 Mar 06 '20 at 10:26
@YaddyVirus - I had an error in how I was getting `$_GET['item']` - it _should_ work now. – CD001 Mar 06 '20 at 10:32
So I enabled error reporting and reverted back to my original script. This is what I'm getting. Notice: Undefined variable: result in /home/siteincharge/public_html/nutsnboltz.com/productpage.php on line 110 Fatal error: Call to a member function fetch_assoc() on a non-object in /home/siteincharge/public_html/nutsnboltz.com/productpage.php on line 110 I guess you were right? – YaddyVirus Mar 06 '20 at 10:32
As far as my understanding goes, you're pulling in the argument using get and then running the function from it inside the if condition storing the result in `$result`. Am I correct? Also, do you mind telling why you're putting `$item` inside the if statement as well? – YaddyVirus Mar 06 '20 at 10:36
1

It's just a sanity check - if `$item` is `false` there's no point calling the function. You'll probably want to take a look at sanitising user input and preventing SQL injection now: https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 ;) – CD001 Mar 06 '20 at 10:38

Can't make MySQL queries in PHP webpage

1 Answers1