How to get single value from MySQL query in PHP

Question

I know this post is similar to many other Stack Overflow questions. However, they quite didn't help to sort out this issue. I want the query to return single value and store it in variable. The SQL query is working fine in the database but not with PHP. Where have I gone wrong here?

$datelink = $_GET['bdate'];
$nid = $mysqli->query("select `newsletterId` from `newsletter` where ndate='$datelink'")->fetch_object()->name;

@Mario, I did this but didn't work.. $nid = $mysqli->query("select newsletterId from newsletter where ndate='30-01-2015'")->fetch_object()->newsletterId; — floCoder
– floCoder, Commented Jan 29, 2015 at 22:41
Your code is open to SQL injection attacks. Please read this: stackoverflow.com/questions/60174/… — Andy Lester
– Andy Lester, Commented Jan 29, 2015 at 22:50
@Nephil: if ndate is a DATE column (and it should be, since you're using it to store dates) you need to use the yyyy-mm-dd date format, like 2015-01-30. You won't get results when you use other formats. — Arjan
– Arjan, Commented Jan 29, 2015 at 22:53

xxdavid · Accepted Answer · 2015-02-02 19:10:25Z

1

You should initiate your connection before quering the database.

$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: " . $mysqli->connect_error;
}

It's also dangerous to just append your string from $_GET. Your site can be attacked with SQL injection. You definitely should use PDO with prepared statements. Or some another library like that, I like dibi for example.

Update: You are accessing column name that doesn't exist. It should throw a notice (PHP Notice: Undefined property: stdClass::$name). If you don't see notices and you are developing locally (and you definitely should), you can enable it in your php.ini file with error_reporting = E_ALL.

You probably want column newsletterId. So altogether:

$mysqli = new mysqli('example.com', 'user', 'password', 'database');
if ($mysqli->connect_errno) {
    echo 'Failed to connect to MySQL: ' . $mysqli->connect_error;
}
$dateRaw = $_GET['bdate']; // for example '22.12.2012', if this value is already in format YYYY-MM-DD, you can step next line and use it
$date = date('Y-m-d', strtotime($dateRaw));


$statement = $mysqli->prepare("SELECT `newsletterId` FROM `newsletter` WHERE `ndate`=? LIMIT 1");
$statement->bind_param('s', $date); // replace the question mark with properly escaped date
$statement->execute();
$result = $statement->get_result();

if ($result->num_rows) {
    $newsletterObject = $result->fetch_object();
    $newsletterId = $newsletterObject->newsletterId; // you had $newsletterObject->name here
} else {
    echo "Newsletter with the date doesn't exist";
}
$statement->close();

edited Feb 2, 2015 at 19:10

answered Jan 29, 2015 at 22:47

xxdavid

612 silver badges7 bronze badges

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

5 Comments

mpen Over a year ago

Pretty sure mysqli supports prepared statements; he doesn't need to switch libs, just needs to use it appropriately.

floCoder Over a year ago

I check the connection .. it seems okay but not working

xxdavid Over a year ago

@Nephil, what's your DB schema (structure)?

floCoder Over a year ago

@xxdavid , here it is: newsletterId smallint(6) ndate date edition tinytext pdfURL text webURL mediumtext hasRadio tinyint(1) radioLink text welcomeNote mediumtext poem mediumtext

floCoder Over a year ago

Thanks a lot xxdavid, however i managed to get it using full while loop algorithm and I have posted the solution too...

taco · Accepted Answer · 2015-01-29 22:43:51Z

0

$datelink=$_GET['bdate'];
$nid= $mysqli->query("SELECT `newsletterId` FROM `newsletter` WHERE ndate='$datelink' LIMIT 1")->fetch_object()->name;

answered Jan 29, 2015 at 22:43

taco

1,38317 silver badges32 bronze badges

Comments

floCoder · Accepted Answer · 2015-02-02 22:34:02Z

0

Initially I thought that there is easy way to fetch single value but couldn't find it. So have to use full code as below to get the value.

$sql3="select newsletterId from newsletter where ndate='$bdate' limit 1";
$result3 = $conn->query($sql3);
    if ($result3->num_rows > 0) {

    while($row3 = $result3->fetch_assoc()) {                    
    $nid=$row3['newsletterId']; //Here is where the single value is stored.

    }
    } else {
        echo "0 results";
    }

answered Feb 2, 2015 at 22:34

floCoder

4311 gold badge7 silver badges21 bronze badges

1 Comment

user3402040 Over a year ago

Yes see this post : stackoverflow.com/questions/20878089/…

Collectives™ on Stack Overflow

How to get single value from MySQL query in PHP

3 Answers 3

5 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related