PDO error help

[rosse88]

Okay, so I am not entirely sure how to fix this. Im attempting to make a tag feature for my site but I am getting:

Fatal error: Call to a member function prepare() on a non-object in /Users/Home/Sites/functions/users.php on line 10

Here is the code that relates to this.

First is the connect file (connect.php)

<?php

mysql_connect('localhost','root','myphpadmin');
mysql_select_db('luminregister');

$dbhost = "localhost";
$dbuser = "root";
$dbpass = "myphpadmin";
$dbname = "luminregister";



// connect using PDO
try { 
    $dbh = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}
catch(PDOException $e) { 
    echo $e->getMessage();
    die();
}

?>

The next file of merit is users.php which for this matter is

<?php
function register_tags($tags) {
    $tagname = explode( ',', $tags );

    foreach (range(1,count($tagname)) as $tagnumber) {
        $bind[] = ':tag_name'.$tagnumber;
    }

    $sql = sprintf("INSERT INTO `question_tags` (tag_name) VALUES ( %s )", implode( '),(', $bind ));
    $stmt = $dbh->prepare($sql);
    foreach( $tagname as $k => $tag ) {
        $stmt->bindValue( ":tag_name" . ++$k, $tag_name );
    }
    $stmt->execute();
}
?>

The idea is to take the tags from a single input separated by commas and then input them into sql inside independent columns (each labled tag_name1 through tag_name10). This is the only PDO that ive had issues with (my other cascading drops work just fine). Normally, my understandins is that the prepare() is not calling the pdo, but Im almost certain this one is just fine. So I could use some serious help here.

Another option, being that I completely missed the mark for the stated goal, would be alternative advice.

Thank you in advance for any assistance.

[pritaeas]

The error message states that $dbh is incorrect. Are you sure you included the connect script?

[rosse88]

What you see is what is being used. The test script Im using is

<?php
include 'core/session.php';
if(empty($_POST) === false) {
$tags = $_POST['tag'];
register_tags($tags);
}
?>
<form method="post">
<input type="text" name="tag">
<input type="submit" value="Submit Tags">
</form>

Sessions.php is

<?php
session_start();
error_reporting();
require 'database/connect.php';
require 'functions/users.php';
require 'functions/general.php';
// other stuff here that doesn't apply such as page lang,etc //
?>

[diafol]

Ok, had a look at the PDO stuff, and came up with this - just slightly different to yours. It works for me with a table with the fields: tag1,tag2,tag3,tag4,tag5,tag6

<?php
    $dbhost = "localhost";
    $dbuser = "root";
    $dbpass = "";
    $dbname = "diafol";
    // connect using PDO
    try { 
        $dbh = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
    }
    catch(PDOException $e) { 
        echo $e->getMessage();
        die();
    }

    $max_tags = 6; //maximum allowed tags for DB

    $tags = "one, two, ,    , three, , 'four, 'five, six, seven, eight";  //badly formed input

    $tagname = array_map("trim", explode( ',', $tags )); //trimmed items into array
    $clean_tags = array_values(array_filter($tagname)); //strip empty items from bad input
    $upper_limit = min($max_tags,count($clean_tags)); //get max number of tags from input
    for($x=1;$x<=$upper_limit;$x++){
        $current = 'tag' . $x;
        $tagfields[] = '`' .$current . '`'; //fieldnames in SQL
        $tagbind[] = ':' . $current; //placeholders for values in SQL
        $tagvalue[':'.$current] = $clean_tags[$x-1]; //build array for execute replacement  
    }
    $tagfieldstring = implode(",",$tagfields); //make a string for SQL
    $tagbindstring = implode(",",$tagbind); // make a string for SQL

    $sql = "INSERT INTO `tags` ($tagfieldstring) VALUES ($tagbindstring)"; //echo 'SQL: ' . $sql . '<pre>';print_r($tagvalue);echo '</pre>';exit;
    $stmt = $dbh->prepare($sql);
    $stmt->execute($tagvalue);

?>

Just uncomment the last comment line to run a test.

[rosse88]

Okay, so that answer was a mixed bag. I did get this from the test run:

SQL: INSERT INTO `question_tags` (`tags_name1`,`tags_name2`,`tags_name3`,`tags_name4`,`tags_name5`,`tags_name6`,`tags_name7`,`tags_name8`) VALUES (:tags_name1,:tags_name2,:tags_name3,:tags_name4,:tags_name5,:tags_name6,:tags_name7,:tags_name8)
Array
(
    [:tags_name1] => one
    [:tags_name2] => two
    [:tags_name3] => three
    [:tags_name4] => 'four
    [:tags_name5] => 'five
    [:tags_name6] => six
    [:tags_name7] => seven
    [:tags_name8] => eight
)

When I changed the max limiter to 10 (as opposed to the 6). However, when I run it without the test as such:

    function register_tags($tags) {
        $max_tags = 10; //maximum allowed tags for DB

        $tagname = array_map("trim", explode( ',', $tags )); //trimmed items into array
        $clean_tags = array_values(array_filter($tagname)); //strip empty items from bad input
        $upper_limit = min($max_tags,count($clean_tags)); //get max number of tags from input
        for($x=1;$x<=$upper_limit;$x++){
            $current = 'tags_name' . $x;
            $tagfields[] = '`' .$current . '`'; //fieldnames in SQL
            $tagbind[] = ':' . $current; //placeholders for values in SQL
            $tagvalue[':'.$current] = $clean_tags[$x-1]; //build array for execute replacement  
        }
        $tagfieldstring = implode(",",$tagfields); //make a string for SQL
        $tagbindstring = implode(",",$tagbind); // make a string for SQL

        $sql = "INSERT INTO `question_tags` ($tagfieldstring) VALUES ($tagbindstring)";
        $stmt = $dbh->prepare($sql);
        $stmt->execute();
    }
$tags = $_POST['tag'];
register_tags($tags);
?>

<form method="post">
<input type="text" name="tag">
<input type="submit" value="Submit Tags">
</form>

I get this error now:

Warning: implode() [function.implode]: Invalid arguments passed in /Users/Home/Sites/connect2.php on line 28

Warning: implode() [function.implode]: Invalid arguments passed in /Users/Home/Sites/connect2.php on line 29

Fatal error: Call to a member function prepare() on a non-object in /Users/Home/Sites/connect2.php on line 32

[diafol]

The

$max_tags = 10; 

must be equal to or less than the number of tag fields in your DB. We could calculate this from a query, but this is just a quick script.

I did the same as you but I didn't get the implode errors. If the $max_tags AND the number of values in the input string are greater than the number of db fields, then the query fails (expected behaviour).

//EDIT

$stmt->execute();

requires the array:

$stmt->execute($tagvalue);

or the placeholders do not get replaced. However, I can't see where we go wrong with the implode. Also the 'prepare' error sugegsts that the connection has not been made, as previously mentioned by pritaeas.

[rosse88]

Im running 10 fields exactly in the DB. I (just to be sure) ran it with the max fields marked 6 through 10 individually and still got the same errors.

[diafol]

OK, perhaps it's because of the $_POST variable. I was using a bog-standard string. I'll try the form and see what I get.

[rosse88]

The implode issue was due to lack of a definition:

$tagfields[] = array();
$tagbind[] = array();

That got rid of the implode issue right off. However the prepare still stands. And while I know that it indicates the connection not being made, I dont seem to see where Im lacking it. I definte the PDO in the connection file. Then (in this case) am even continuing in the same file.

EDIT

Should also note that I have also updated the pdo connection to this:

$config['db'] = array(
    'host'      => 'localhost',
    'username'  => 'root',
    'password'  => 'myphpadmin',
    'dbname'    => 'luminregister'
);
try { 
    $dbh = new PDO('mysql:host=' . $config['db']['host'] . ';dbname=' . $config['db']['dbname'] . '', $config['db']['username'], $config['db']['password']);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e) {
    die ($e->getMessage());
}

Which does work when I check/insert other files directly. Simply not this one.

[diafol]

OK, seems I don't get your errors. This is my updated code with your form - it doesn't seem to be down to weird encoding. Note I comment out the exit statement.

<?php
    $dbhost = "localhost";
    $dbuser = "root";
    $dbpass = "";
    $dbname = "diafol";
    // connect using PDO
    try { 
        $dbh = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
    }
    catch(PDOException $e) { 
        echo $e->getMessage();
        die();
    }

    $max_tags = 20; //maximum allowed tags for DB

    if(isset($_POST['tag'])){ 
        //$tags = "one, two, ,    , three, , 'four, 'five, six, seven, eight";  //badly formed input
        $tags = $_POST['tag'];
        $tagname = array_map("trim", explode( ',', $tags )); //trimmed items into array
        $clean_tags = array_values(array_filter($tagname)); //strip empty items from bad input
        $upper_limit = min($max_tags,count($clean_tags)); //get max number of tags from input
        for($x=1;$x<=$upper_limit;$x++){
            $current = 'tag' . $x;
            $tagfields[] = '`' .$current . '`'; //fieldnames in SQL
            $tagbind[] = ':' . $current; //placeholders for values in SQL
            $tagvalue[':'.$current] = $clean_tags[$x-1]; //build array for execute replacement  
        }
        $tagfieldstring = implode(",",$tagfields); //make a string for SQL
        $tagbindstring = implode(",",$tagbind); // make a string for SQL



        $sql = "INSERT INTO `tags` ($tagfieldstring) VALUES ($tagbindstring)";
        echo 'SQL: ' . $sql . '<pre>';print_r($tagvalue);echo '</pre>';//exit;
        $stmt = $dbh->prepare($sql);
        $stmt->execute($tagvalue) or die('BAD QUERY, NAUGHTY QUERY, GO TO YOUR ROOM QUERY');

    }
?>

<form method="post">
<input type="text" name="tag" value="one, two, ,    , three, , 'four, 'five, six, seven, eight">
<input type="submit" value="Submit Tags">
</form>

When it fails, I get the expected:

SQL: INSERT INTO `tags` (`tag1`,`tag2`,`tag3`,`tag4`,`tag5`,`tag6`,`tag7`,`tag8`) VALUES (:tag1,:tag2,:tag3,:tag4,:tag5,:tag6,:tag7,:tag8)
Array
(
    [:tag1] => one
    [:tag2] => two
    [:tag3] => three
    [:tag4] => 'four
    [:tag5] => 'five
    [:tag6] => six
    [:tag7] => seven
    [:tag8] => eight
)
BAD QUERY, NAUGHTY QUERY, GO TO YOUR ROOM QUERY

[diafol]

$tagfields = array();
$tagbind = array();

Should do.

Can I suggest that you paste the connection code directly into the page for now to see if a valid connection is made and query can be run?

[rosse88]

Okay, so we're making headway (ty btw). This is the entire sheet in 1:

<?php
$config['db'] = array(
    'host'      => 'localhost',
    'username'  => 'root',
    'password'  => 'myphpadmin',
    'dbname'    => 'luminregister'
);

try { 
    $dbh = new PDO('mysql:host=' . $config['db']['host'] . ';dbname=' . $config['db']['dbname'] . '', $config['db']['username'], $config['db']['password']);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e) {
    die ($e->getMessage());
}

    if(isset($_POST['tag'])) {

        $tagfields[] = array();
        $tagbind[] = array();

        $max_tags = 6;

        $tagname = array_map("trim", explode( ',', $tags ));
        $clean_tags = array_values(array_filter($tagname));
        $upper_limit = min($max_tags,count($clean_tags));
        for($x=1;$x<=$upper_limit;$x++){
            $current = 'tags_name' . $x;
            $tagfields[] = '`' .$current . '`';
            $tagbind[] = ':' . $current;
            $tagvalue[':'.$current] = $clean_tags[$x-1];  
        }
        $tagfieldstring = implode(",",$tagfields);
        $tagbindstring = implode(",",$tagbind);

        $sql = "INSERT INTO `question_tags` ($tagfieldstring) VALUES ($tagbindstring)";
        $stmt = $dbh->prepare($sql);
        $stmt->execute($tagvalue);
    }

?>

<form method="post" action="">
<input type="text" name="tag">
<input type="submit" value="Submit Tags">
</form>

Now, I know that its reading

$tagfields[] = array();
$tagbind[] = array();

as the values of the fields. However, which I simply add the array definition later, I get the implode error again. Other then that, even with the error, it added blank entries. So thats an improvement slightly.

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Array' in 'field list'' in /Users/Home/Sites/connect2.php:38 Stack trace: #0 /Users/Home/Sites/connect2.php(38): PDOStatement->execute(NULL) #1 {main} thrown in /Users/Home/Sites/connect2.php on line 38

[diafol]

I suggested this:

$tagfields = array();
$tagbind = array();

Not as you originally had:

$tagfields[] = array();
$tagbind[] = array();

As that will give a multidimensional array - which you don't want.

[rosse88]

Got it working. The array issue was because empty variables were being entered. It literally WASNT an array (stupid me). Other then that, I also forgot to define $tags that last go. Other then that, its running. However, why on earth doesnt it run when I seperate into multiple parts.

[diafol]

Dunno, but I'd keep playing with it. I'm assuming that your file reference is correct otherwise, require should give an error.

You could try adding an echo command to the bottom of the connection script in the require file to see that the file's being accessed:

$config['db'] = array(
    'host'      => 'localhost',
    'username'  => 'root',
    'password'  => 'myphpadmin',
    'dbname'    => 'luminregister'
);
try { 
    $dbh = new PDO('mysql:host=' . $config['db']['host'] . ';dbname=' . $config['db']['dbname'] . '', $config['db']['username'], $config['db']['password']);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e) {
    die ($e->getMessage());
}

echo "It's working";

[rosse88]

Well the "Its working seems to be functional most of the time. But for purpose of this question, its answered enough, lol. Thank you againf or the help.