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, Jan 29 '15 at 22:41
Your code is open to SQL injection attacks. Please read this: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Andy Lester, Jan 29 '15 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, Jan 29 '15 at 22:53
@Nephil string types are not suitable for dates. At some point you'll want to select from a range of dates, which is completely impossible when you are using a string type. Also note that, when sorting strings, `10-02-2015` comes before `30-01-2015`. And `01-12-2016` comes before `10-02-2015`. — Arjan, Jan 29 '15 at 23:03

xxdavid · Answer 1 · 2015-02-02T19:10:25.760

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();

Pretty sure [mysqli supports prepared statements](http://php.net/manual/en/mysqli.prepare.php); he doesn't need to switch libs, just needs to use it appropriately. — mpen, Jan 29 '15 at 22:52
@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, Feb 01 '15 at 23:03
Thanks a lot xxdavid, however i managed to get it using full while loop algorithm and I have posted the solution too... — floCoder, Feb 02 '15 at 22:35

score 0 · Answer 2 · answered Jan 29 '15 at 22:43

0

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

answered Jan 29 '15 at 22:43

taco

1,367
17
32

score 0 · Accepted Answer · answered Feb 02 '15 at 22:34

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";
    }

Yes see this post : http://stackoverflow.com/questions/20878089/php-and-mysql-select-a-single-value — , Oct 23 '15 at 06:09

How to get single value from MySQL query in PHP

3 Answers3