Using MySQL and PHP to extract from a DB

[chrisguk]

I really hope someone can help me because its driving me mad trying to understand what I need to do here.

 

I have read the PDO and prepare statements manual, but just cant work out whats wrong with my code below:

$dsn = "mysql:host=localhost;dbname=maindb";
$user = "root";
$password = "mypass";
 try {
    $pdo = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}
// Database  
$page = isset($_GET['page']) ? $_GET['page'] : 'home';
#$page = $pdo->real_escape_string($page);
#$sql = $mysqli->query("SELECT * FROM url WHERE page = '" . $page . "' LIMIT 1");
$stmt = $pdo->prepare("SELECT * FROM url WHERE page = ? LIMIT 1");
$stmt->execute(array($page));

if ($pdo->connect_errno) {
    printf("Connect failed: %s\n", $pdo->connect_error);
    exit();
}

if ($stmt->num_rows > 0) {
    while ($row = $stmt->fetch_object())
    {
        $title = $row->title;
        $description = $row->descr;
        $keywords = $row->keywords;

    }

} else {

    $title = 'Set Default Title';
    $description = 'Set Default Description';
    $keywords = 'Set Default Keywords';

}

I would be really glad for any assistance

Edited May 13, 2013 by chrisguk

[Jessica]

Between lines 13-14 you need to call bindValue to replace your placeholder with a value.

 

In the future when you ask for help, you need to explain more than just the fact that something somewhere is wrong.

[chrisguk]

Between lines 13-14 you need to call bindValue to replace your placeholder with a value.

 

In the future when you ask for help, you need to explain more than just the fact that something somewhere is wrong.

 

Jess, thank you for your reply.  The output is the default values declared in the bottom half of the code.  Obviously I am expecting it to produce and output based on the page name that has been selected.  For example:

 

I select the home page and the title, keywords and description are pulled from the DB and populated using variables.

 

This is really just the begining as I ultimately want to start populating repetitive text values across the site too in the future.

 

I am extremely new to PDO and prepare statements as for some unknown reason I have been doing things the old way for a long time.

 

When you mention bindValue() between 13-14, would this following example be the correct usage:

$stmt->bindValue($page, PDO::PARAM_STR);

I have tried it like that and still the code is not retrieving the data.  Are there any debug steps I could take to try and understand the function better?

 

Thank you for your patience with me so far 

 

Chris

[mac_gyver]

input parameters can be supplied as an array in the execute() method (all values are treated as strings though.)

 

other than the try/catch around the connection, your code has no error checking in it (the $pdo->connect_errno property doesn't exist and isn't doing anything.) i'm also pretty sure that there isn't an $stmt->num_rows property either. you would use the rowCount ( void ) method, when it exists (is usually better to just fetch/fetchall the rows and test if any rows were fetched.

 

do you have php's error_reporting/display_errors turned full on so that php is helping you?

 

you need to use logic like the following to check for errors -

$dsn = "mysql:host=localhost;dbname=maindb";
$user = "root";
$password = "mypass";
try {
    $pdo = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

$query = " your query statement here ";
if(!$stmt = $pdo->prepare($query)){
    // prepare failed
    echo "<pre>Prepare failed:\n";
    print_r($pdo->errorInfo());
    echo "</pre>";
} else {
    if(!$stmt->execute(array($page))){
        // execute failed
        echo "<pre>Execute failed:\n";
        print_r($stmt->errorInfo());
        echo "</pre>";
    } else {
        // query ran without any errors, you can test if it returned any rows and use them here
        
    }
}

[chrisguk]

 

input parameters can be supplied as an array in the execute() method (all values are treated as strings though.)

 

other than the try/catch around the connection, your code has no error checking in it (the $pdo->connect_errno property doesn't exist and isn't doing anything.) i'm also pretty sure that there isn't an $stmt->num_rows property either. you would use the rowCount ( void ) method, when it exists (is usually better to just fetch/fetchall the rows and test if any rows were fetched.

 

do you have php's error_reporting/display_errors turned full on so that php is helping you?

 

you need to use logic like the following to check for errors -

$query = ("SELECT * FROM url WHERE page = ? LIMIT 1");

 

I added that code and inserted the query above.  I didnt have errors turned on full but I have now.  I ran the above and no errors are produced.

 

based on my previous code what would be the best way to include my desired output, as I am guessing $row is now undefined:

if ($stmt->num_rows > 0) {
    while ($row = $stmt->fetch_object())
    {
        $title = $row->title;
        $description = $row->descr;
        $keywords = $row->keywords;

    }

} else {

    $title = 'Set Default Title';
    $description = 'Set Default Description';
    $keywords = 'Set Default Keywords';

}

[mac_gyver]

for that specific query, you are expecting either zero or one row -

if($row = $stmt->fetchobject()){
    // query matched the one expected row

} else {
    // query matched no row

}

i'm also pretty sure there is no pdo fetch_object() method either.

[Jessica]

I didn't see that he was using the array there. (That's why you need to explain what's actually wrong, so we don't go chasing wild geese)

[chrisguk]

---

Ok I seem to be getting somewhere now.  I used this:

while($row = $stmt->fetch()) {
        print_r($row);
}

and it printed this:

Array ( [id] => 50 [0] => 50 

---

 => home [1] => home [title] => Home [2] => Home [descr] => ssssgdgdfgbooooooooooo [3] => ssssgdgdfgbooooooooooo [keywords] => sdfsfsdf [4] => sdfsfsdf )

So I am guessing the query is working.

 

@Jessica

 

I understand that you probably have thousands of posts regarding similar subjects.  But please understand I am new to this stuff.  I do absolutely appreciate the help though because every piece of advice you give me will help me learn better.

 

Can I ask some advice for the final step please ? 

[chrisguk]

Its ok woohoo

 

I figured it alone

if ($stmt->rowCount() > 0) {
    while ($row = $stmt->fetch())
    {
        $title = $row[1];
    $description = $row[2];
        $keywords = $row[3];

    }

} else {

    $title = 'Set Default Title';
    $description = 'Set Default Description';
    $keywords = 'Set Default Keywords';

}