Converting existing php database function to use mysqli

[Dave]

Hi guys,

Sorry if this is the wrong group (not sure whether I should be posting in mysql or php).

I have inherited a website which uses a php database class and need to convert it to use mysqli, but I am struggling with one of the functions.

Basically this function will bring back the field straight a variable so in the php you could write:

$sql = "SELECT ID FROM DBTABLE WHERE NAME='php'";
$sql = $db->getResult($this->executeQuery($sql),0);

and the result of $sql would be the ID number found.

the function executeQuery($sql) simply runs the query, where as the getResult($sql,0) uses mysql_result($result,$field).

From what i can see, the new mysqli works a completely different way and I can't seem to find a way to simply output a field based on the numeric value given.

the function is currently:

function getResult($result, $cell){
if(mysql_num_rows($result) !=0) {
$this->dbConnect();
$sql = mysql_result($result, $cell);
$this->closeDatabase();
}
else {
$sql ="No result found";
}
return $sql;
}

Hoping someone can point me in the right direction.

Dave.

[Arno Welzel]

Am 2015-11-13 um 13:26 schrieb Dave:

> I have inherited a website which uses a php database class and need
> to convert it to use mysqli, but I am struggling with one of the functions.

[...]

> From what i can see, the new mysqli works a completely different way
> and I can't seem to find a way to simply output a field based on the
> numeric value given.
>
> the function is currently:
>
> function getResult($result, $cell){
> if(mysql_num_rows($result) !=0) {
> $this->dbConnect();
> $sql = mysql_result($result, $cell);
> $this->closeDatabase();
> }
> else {
> $sql ="No result found";
> }
> return $sql;
> }
>
> Hoping someone can point me in the right direction.

Did your read <http://php.net/manual/en/book.mysqli.php>?

Also see the examples here:

<http://php.net/manual/en/mysqli-stmt.fetch.php>
<http://php.net/manual/en/mysqli-stmt.prepare.php>



--
Arno Welzel
http://arnowelzel.de
http://de-rec-fahrrad.de
http://fahrradzukunft.de

[Jerry Stuckle]

On 11/13/2015 7:26 AM, Dave wrote:
> Hi guys,
>
> Sorry if this is the wrong group (not sure whether I should be posting in mysql or php).
>

This is the right group, since you're talking about PHP functions. The
easiest way to tell is to see if the code you're asking about is in the
PHP manual. mysql...() and mysqli...() functions are described in the
manual. OTOH, SQL statements are not in the PHP manual, and should be
asked in the appropriate database group.

> I have inherited a website which uses a php database class and need to convert it to use mysqli, but I am struggling with one of the functions.
>
> Basically this function will bring back the field straight a variable so in the php you could write:
>
> $sql = "SELECT ID FROM DBTABLE WHERE NAME='php'";
> $sql = $db->getResult($this->executeQuery($sql),0);
>
> and the result of $sql would be the ID number found.
>

First of all, this is bad. You need to check the result of
executeQuery() to ensure it returned a result. If for some reason the
query fails, executeQuery() returns false and the getResult() will fail
with a fatal error.

> the function executeQuery($sql) simply runs the query, where as the getResult($sql,0) uses mysql_result($result,$field).
>
> From what i can see, the new mysqli works a completely different way and I can't seem to find a way to simply output a field based on the numeric value given.
>
> the function is currently:
>
> function getResult($result, $cell){
> if(mysql_num_rows($result) !=0) {
> $this->dbConnect();
> $sql = mysql_result($result, $cell);
> $this->closeDatabase();
> }
> else {
> $sql ="No result found";
> }
> return $sql;
> }
>
> Hoping someone can point me in the right direction.
>
> Dave.
>

Don't connect and disconnect each time. Connect once when you first
need to access the database, and close when you've completed all operations.

Mysqli has two interfaces. One is the object oriented interface you're
using here; the other is a functional interface similar to the old mysql
interface. Personally, I prefer the OO interface.

Not knowing what else you have in your class, I'll just keep it simple
here. I would do it more like (assuming the function is a member of
your database class):

function getId($name) {
$sql = "SELECT id FROM DBTABLE WHERE NAME='$name'";
$result = $this->query($sql); // Database already open
if ($result === false || $result->num_rows() == 0)
return false;
$row = $result->fetch_assoc();
return $row['id'];
}

Not tested, but hopefully this gives you some ideas.

--
==================
Remove the "x" from my email address
Jerry Stuckle
jstu...@attglobal.net
==================

[Dave]

You are a star Jerry, thank you.

Dave.