Avatar of Wanda Marston
Wanda Marston
Flag for Canada asked on

What is the proper code to pass to the database to retrieve the proper information?

I receive this error message when trying to retrieve information from a database with a prepared statement - We have no data for this ID     

I know that there is information in the database. I am new to prepared statements. 

Following is my code:<?php
$id = isset($_GET['id']) ? $_GET['id'] : false; if ($id) {  } $query = <<< QUERY SELECT notices.upload_id,  notices.description, notices.amount, notices.currency,  notices.location,  users.email FROM notices INNER JOIN users ON notices.users_id = users.id WHERE notices.users_id= ? QUERY; if (! $stmt = mysqli_prepare($db, $query)) { die("There was a problem preparing the statement"); } mysqli_stmt_bind_param($stmt, "d", $id); mysqli_stmt_bind_result($stmt, $upload_id, $description, $amount, $currency, $location,  $email); mysqli_stmt_execute($stmt); if (mysqli_stmt_fetch($stmt)) {                               $html = <<< EOT     <form action="ContactPoster.php" method="post">         <p>Description: {$description}</p>         <p>Amount: {$amount}</p>         <p>Currency: {$currency}</p>         <p>Location: {$location}</p>                 <input type="hidden" name="toemail" value="{$email}"> EOT;       echo $html; } else {     echo "We have no data for this ID"; } ?>

Open in new window


DatabasesPHP

Avatar of undefined
Last Comment
Chris Stanyon

8/22/2022 - Mon
Chris Stanyon

Hey Wanda,

Basically, your code looks correct, so I would double-check that you do actually have data in your database. I would start by running the simplest query you can just to get things working. Whilst debugging, I would always advise that you turn on error_reporting.

May not be an issue, but your ID is likely to be an integer, not a double, so use "i" in the bind_param call instad of "d".

I'm also assuming you're only showing us part of your script and the DB connection is elsewhere !!

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);

// connect to DB

$id = isset($_GET['id']) ? $_GET['id'] : false;
var_dump($id); // check the input

$query = <<< QUERY
SELECT users.email 
FROM users
WHERE users.id = ?
QUERY;

if (! $stmt = mysqli_prepare($db, $query)) {
    die("There was a problem preparing the statement");
}

mysqli_stmt_bind_param($stmt, "i", $id);
mysqli_stmt_bind_result($stmt, $email);
mysqli_stmt_execute($stmt);

if (mysqli_stmt_fetch($stmt)) {
    var_dump($email);
} else {
    echo "We have no data for this ID";
    echo mysqli_stmt_error($stmt);
}

Open in new window

Wanda Marston

ASKER
Below is a screenshot of the "notices" table in the database

Chris Stanyon

OK ?? Not sure of the relevance of that screenshot. What it does highlight however is that you have a 1-to-many relationship between a User and the Notices, but your code is assuming a one-to-one relationship, so you'll need to address that at some point (basically you'll need to call fetch in a loop).

Before you even start to deal with that though, you need to run my code and see what you get. It'll give you a decent starting point. There's no point in moving on until you can get the basics working.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Wanda Marston

ASKER
Okay thanks,
Yes, the call to the database is almost at the top of the page.
This is the error message I received with your code.

bool(false) We have no data for this ID    
Chris Stanyon

OK,

In that case, you're not calling your page with an ID. Your code is expecting a querystring with a key of id:

yourdomain.com/somepage.php?id=123

The message that you're getting means that you're not sending that querystring, so the ID is false
Wanda Marston

ASKER
Okay, so that was the reason why I attached a screenshot of the "notices" table. I have an "upload_id" column and a "users_id" column.

When the page comes back it shows the "upload_id" in the browser as part of the URL and the information would be in that "upload_id" row BUT the code says that there is no data for that id. SO should the column just say "id" and not "upload_id"?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Chris Stanyon

The querystring keys have nothing to do with the database. They're just the names used to pass data into your script. The key doesn't need to be the same as the database column. The fact is that your code has this:

$id = isset($_GET['id']) ? $_GET['id'] : false;

So the only way your code is ever going to work is if your query string has a GET key of id:

yourdomain.com/somepage.php?id=123

Once you've passed that data in, then the value of the $_GET['id'] key will be assigned to the $id variable (which in the example above would be 123).
Wanda Marston

ASKER
Okay so that means I have to put "id" somewhere else?

Maybe this line? - mysqli_stmt_bind_result($stmt, $id, $description, $amount, $currency, $location, $email);

In this part of the code?

if (! $stmt = mysqli_prepare($db, $query)) {
die("There was a problem preparing the statement");
}

mysqli_stmt_bind_param($stmt, "d", $id);
mysqli_stmt_bind_result($stmt, $id, $description, $amount, $currency, $location, $email);
mysqli_stmt_execute($stmt);

if (mysqli_stmt_fetch($stmt)) {
Chris Stanyon

No. You're not trying to bind the result.

Let's have a look at the main parts of your script.

// get whatever ID is passed into the page via the QueryString
$id = isset($_GET['id']) ? $_GET['id'] : false;

// Build the query with a placeholder for the ID
$query = <<< QUERY
SELECT notices.upload_id,  notices.description, notices.amount, notices.currency,  notices.location,  users.email 
FROM notices INNER JOIN users ON notices.users_id = users.id
WHERE notices.users_id = ?
QUERY;

// Bind the ID that's passed in to the placeholder
mysqli_stmt_bind_param($stmt, "d", $id);

Open in new window

So, for your code to work, you need to pass in the ID that you want to use in your query. You do this by passing the value in the querystring.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Wanda Marston

ASKER
Okay so here - ON notices.users_id = id intead of users.id.

SELECT notices.upload_id,  notices.description, notices.amount, notices.currency,  notices.location,  users.email 
FROM notices INNER JOIN users ON notices.users_id = id
WHERE notices.users_id = ?

Open in new window


Chris Stanyon

No. Why are you changing the query ?

Your code will work if you pass in the ID via the query string. Here's what you need to do:

1 - pass an ID into your script. You do this with the querystring:  somepage.php?myid=123

2 - build a query to use some dynamic data with a placeholder (the question mark) - SELECT * FROM someTable WHERE someColumn = ?

3 - now push that querystring value into the query's placeholder:

$someValue = $_GET['myid']; // grab the value from the querystring
mysqli_stmt_bind_param($stmt, "d", $someValue); // bind $someValue to the question mark of your query
lenamtl

Hi,

I would recommend to test your MySQL query directly in PHPMyAdmin.
If this fail you will get the error and this will help you to fix it.
If there is no errors then this mean something in your code is wrong.
Make sure the userid exist and has notices.

As Chris said you need to pass the ID somewhere in the query.
Passing the ID through the url is the common way.
But there are other mathod depending of the needs.

I have checked the printscreen of the "notices table" I saw some of the notices does not have userid
So instead of I would use NULL and I would fix the code to prevent this so a userid is required in your server side validation.

Also if you are starting your project I would use PDO over Mysqli as it is more easy and more flexible to my opinion..
I'm using this backen this is well written and is secure, so it is very good to learn (PDO & AJAX OOP).
https://codecanyon.net/item/advanced-security-php-registerlogin-system/5282621
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Wanda Marston

ASKER
I don't have a column in the notices table that just says id. the id column is upload_id. SO the page that passes the information through the URL has this code: echo "<a href=\"ContactPosterYE.php?upload_id= $row[0]\" style='text-decoration:none'>$row[1], $row[2], $row[3], $row[4]</a></p>\n";

ASKER CERTIFIED SOLUTION
Chris Stanyon

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Wanda Marston

ASKER
Believe me, I am trying to make sense of it all and in the past have never found SQL to be that tricky.

What I have is another website with this coding and that page works fine. It retrieves what is needed from the database and enters it on the page.

SO, I tried to copy that coding for THIS site and there are a few differences, but it is not working. I will have to investigate this a bit further.




Chris Stanyon

I would personally recommend that you take this step by step - you've currently got several steps involved and I think that's confusing you.

Step 1 - learn how to pass data from one page to another in the query string. Unless you totally 'get' that, you're always going to struggle doing what you need to do. I've explained a few times why your code isn't working, so you really do need to digest that. Whatever key you use in your QueryString, you need to use exactly that same key in your PHP - nothing else will work, no matter how much you want it to.

Currently you have this:

// querystring
ContactPosterYE.php?upload_id=XXX

// php
$id = isset($_GET['id']) ? $_GET['id'] : false;

No matter what you do, that simply won't work. The query string key is upload_id but you're attempting to GET the id - THEY ABSOLUTELY MUST BE THE SAME, so either:

ContactPosterYE.php?id=XXX
$id = isset($_GET['id']) ? $_GET['id'] : false;

OR

ContactPosterYE.php?upload_id=XXX
$id = isset($_GET['upload_id']) ? $_GET['upload_id'] : false;

Copy/paste solutions will ALWAYS fail unless you totally understand the code you're pasting.

And for the record - it's not the SQL part that you're failing on - it's the passing of data from one page to another via the querystring.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Wanda Marston

ASKER
Yes, you are right. ALL the comments have been appreciated and I realize that I don't understand how some of this works.
I JUST got it working but I don't know exactly how.
I changed the upload_id to just id in the database and the page before the ContactPosterYE.php. I also got rid of some of the rows in the notices table. NOT that I am saying the latter was necessarily the problem but I thought I may as well get rid of some stuff that doesn't matter.

I will review all of the comments. I am learning but it is a slow process when one has other things to deal with on a daily basis.

Following is my code:
<?php

$id = isset($_GET['id']) ? $_GET['id'] : false;

if ($id) {
    
}

$query = <<< QUERY

SELECT notices.users_id, notices.description, notices.amount, notices.currency, notices.location, users.email 
FROM notices INNER JOIN users ON notices.users_id = users.id
WHERE notices.id = ?
QUERY;

if (! $stmt = mysqli_prepare($db, $query)) {
die("There was a problem preparing the statement");
}

mysqli_stmt_bind_param($stmt, "d", $id);
mysqli_stmt_bind_result($stmt, $user_id, $description, $amount, $currency,  $location,  $email);
mysqli_stmt_execute($stmt);

if (mysqli_stmt_fetch($stmt)) {
                              
$html = <<< EOT
    <form action="ContactPoster.php" method="post">
         <p>Description: {$description}</p>
        <p>Amount: {$amount}</p>
        <p>Currency: {$currency}</p>
        <p>Location: {$location}</p>
       
        <input type="hidden" name="toemail" value="{$email}">
EOT;
 
    echo $html;

} else {
    echo "We have no data for this ID";
}

?>

Open in new window

Chris Stanyon

Hey Wanda,

Glad you've got it working, but if you don't know how, then unfortunately you're gonna struggle to move forward, and waste huge amount of time throwing code at your screen in the 'hope' that something works. Time is money as they say and your approach is going to get really costly.

Like I said earlier, the easiest way to learn this stuff is one step at a time. An hour spent learning about querystrings and GET arrays would have saved your hours of messing about. I totally understand that it's difficult sometimes, but I genuiinely believe that its a false economy to skip the learning because we don't have time.

Good luck with your project :)