Doesn't insert data by PDO

[gilgalbiblewheel]

I’m trying to insert the data using PDO but it’s not working. I echo and it shows all the data that’s being inserted, but it doesn’t do it:

    for($f=0; $f<count($bookSpoke4); $f++){
        $insert = "INSERT INTO ".strtolower($bookTitle[0]).$chapter[0]." (date_time, book_spoke, chapter_spoke, rel_book, rel_book_title, rel_chapter, summary, url_links)
    VALUES ('".date("Y-m-d H:i:s")."', ".$bookSpoke4[$f].", ".$chapterSpoke4[$f].", ".$book4[$f].", '".$bookTitle4[$f]."', ".$chapter4[$f].", '', 'spoke=".$spoke."&b1=".$b1."&c1=".$c1."&b2=".$book4[$f]."&c2=".$chapter4[$f]."')";
    echo $insert."<br /><br /><br />";
        //$affected_rows = $db->exec($insert);
        $db->exec($insert);
        
    }
    //$db->commit();

[colshrapnel]

First of all you have to set proper error reporting mode for pdo. After that PHP will tell you what’s wrong with the query.

Besides, exec() should never be used for the dynamic queries, but it have to be a prepared statement instead

[droopsnoot]

And show us what it echoes - maybe there’s something within the data that is causing it to parse badly, for example, that might not be an issue if you used a prepared statement.

[gilgalbiblewheel]

Here it is:
INSERT INTO isaiah10 (date_time, book_spoke, chapter_spoke, rel_book, rel_book_title, rel_chapter, summary, url_links)
VALUES (‘2016-03-16 06:39:18’, 1, 10, 1, ‘Genesis’, 10, ’
+ kingdom’, ‘spoke=10&b1=23&c1=10&b2=1&c2=10’)

INSERT INTO isaiah10 (date_time, book_spoke, chapter_spoke, rel_book, rel_book_title, rel_chapter, summary, url_links)
	VALUES ('2016-03-16 06:39:18', 1, 10, 1, 'Genesis', 32, '
+ kingdom', 'spoke=10&b1=23&c1=10&b2=1&c2=32')


INSERT INTO isaiah10 (date_time, book_spoke, chapter_spoke, rel_book, rel_book_title, rel_chapter, summary, url_links)
	VALUES ('2016-03-16 06:39:18', 2, 10, 2, 'Exodus', 10, '
+ kingdom', 'spoke=10&b1=23&c1=10&b2=2&c2=10')

...

[gilgalbiblewheel]

I have this at the beginning:
try {
$db = new PDO(“mysql:host=localhost;dbname=myportfolio”, “root”, “”);
}catch(PDOException $e){
echo $e->getMessage();
}

[colshrapnel]

You may want to change it this way

$host = 'localhost';
$db   = 'myportfolio';
$user = 'root';
$pass = '';
$charset = 'utf8';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$opt = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
$pdo = new PDO($dsn, $user, $pass, $opt);

It will make PDO report errors by means of throwing exceptions. Here is the tutorial on PDO I wrote, you may find it worth to read, especially regarding prepared statements and error reporting.

[gilgalbiblewheel]

@colshrapnel

$host = ‘localhost’;
$db = ‘myportfolio’;
$user = ‘root’;
$pass = ‘’;
$charset = ‘utf8’;

$dsn = “mysql:host=$host;dbname=$db;charset=$charset”;
$opt = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
];
$pdo = new PDO($dsn, $user, $pass, $opt);

Fatal error: Call to a member function query() on string in C:.…biblewheel-php\index.php on line 173:

if(($b1!=="") || ($c1!=="")){
    // Step 2: Construct a query

    $sql = "SELECT * FROM ".$dbTable3." WHERE (book = '" .$b1. "' AND chapter= '" .$c1. "') ORDER BY id ASC";
    
    // Step 3: Send the query
    $result = $db->query($sql);  // line 173
    
    // Step 4: Iterate over the results
    while($row = $result->fetch(PDO::FETCH_ASSOC)) {
        //print_r($row);
        $id[] = $row['id'];
        $bookSpoke[] = $row['book_spoke'];
        $chapterSpoke[] = $row['chapter_spoke'];
        $verseSpoke[] = $row['verse_spoke'];
        $bookTitle[] = $row['book_title'];
        $book[] = $row['book'];
        $chapter[] = $row['chapter'];
        $verse[] = $row['verse'];
        $recordType[] = $row['recordType'];
        $textData[] = $row['text_data'];
        $bSpokeCycle[] = $row['b_spoke_cycle'];
        $cSpokeCycle[] = $row['c_spoke_cycle'];
        $vSpokeCycle[] = $row['v_spoke_cycle'];
    }
}else{
    echo $answer;
    }

[colshrapnel]

I forgot tot change $pdo to $db in the code above.

[gilgalbiblewheel]

@colshrapnel

$db

oh nice! THanks! I was using the old mysql coding. And making the transfer made me pretty much start from scratch. I wish there was a systematic tutorial like w3schools.

[gilgalbiblewheel]

but my url extension is:
?dowhat=insindb&spoke=10&b1=23&c1=10&b2=1&c2=10&order=date&keywords=kingdom;hand

And in my code i want to insert in db when dowhat is on and equal to insindb.

I was getting errors that $dowhat isn’t defined.

<?php

if( isset($_GET[“dowhat”]) ):
$dowhat = $_GET[“dowhat”];
endif;

/*if( isset($_GET["dowhat"]) ):
    $dowhat = $_GET["dowhat"];
endif;*/
//if( !isset($_GET["dowhat"]) ):
if(isset($dowhat)=="insindb"){
?>
<span style="font-size: 14px; font-weight: bold;">
          <a href="http://127.0.0.1/biblewheel-php/?<?php echo "spoke=".$spoke."&b1=".$b1."&c1=".$c1."&b2=".$b2."&c2=".$c2."&order=".$order."&keywords=".$kw; ?>" title="<?php echo "insert in ".strtolower($bookTitle[0]).$chapter[0]; ?>">Inserted! (return)</a>
      </span><br /><br />
<?php
echo "Inserted!";
}else{
?>
<span style="font-size: 14px; font-weight: bold;">
          <a href="http://127.0.0.1/biblewheel-php/?<?php echo "dowhat=insindb&spoke=".$spoke."&b1=".$b1."&c1=".$c1."&b2=".$b2."&c2=".$c2."&order=".$order."&keywords=".$kw; ?>" title="<?php echo "insert in ".strtolower($bookTitle[0]).$chapter[0]; ?>">Insert in Database</a>
      </span><br /><br />
<?php
}
//endif;


//if( !isset($_GET["dowhat"]) ):
if(isset($dowhat)=="update"){
?>
<span style="font-size: 14px; font-weight: bold;">
          <a href="http://127.0.0.1/biblewheel-php/?<?php echo "spoke=".$spoke."&b1=".$b1."&c1=".$c1."&b2=".$b2."&c2=".$c2."&order=".$order."&keywords=".$kw; ?>" title="<?php echo "update ".strtolower($bookTitle[0]).$chapter[0]; ?>">Updated! (return)</a>
      </span><br />
<?php
}else{
?>
<span style="font-size: 14px; font-weight: bold;">
          <a href="http://127.0.0.1/biblewheel-php/?<?php echo "dowhat=update&spoke=".$spoke."&b1=".$b1."&c1=".$c1."&b2=".$b2."&c2=".$c2."&order=".$order."&keywords=".$kw; ?>" title="<?php echo "update ".strtolower($bookTitle[0]).$chapter[0]; ?>">Update Database</a>
      </span><br />
<?php
}
//endif;

if($dowhat=="insindb"){
    for($f=0; $f<count($bookSpoke4); $f++){
        $insert = "INSERT INTO ".strtolower($bookTitle[0]).$chapter[0]." (date_time, book_spoke, chapter_spoke, rel_book, rel_book_title, rel_chapter, summary, url_links)
    VALUES ('".date("Y-m-d H:i:s")."', ".$bookSpoke4[$f].", ".$chapterSpoke4[$f].", ".$book4[$f].", '".$bookTitle4[$f]."', ".$chapter4[$f].", '', 'spoke=".$spoke."&b1=".$b1."&c1=".$c1."&b2=".$book4[$f]."&c2=".$chapter4[$f]."')";
    echo $insert."<br /><br /><br />";
        //$affected_rows = $db->exec($insert);
        $db->exec($insert);
        
    }
    //$db->commit();
}
if($dowhat=="update"){
    //$upd3 = "UPDATE ".strtolower($bookTitle[0]).$chapter[0]." SET summary = '".$kwstring."', date_time = '".date("Y-m-d H:i:s")."', url_links= 'spoke=".$spoke."&b1=".$b1."&c1=".$c1."&b2=".$book4[$f]."&c2=".$chapter4[$f]."' WHERE rel_book = ".$b2." AND rel_chapter = ".$c2." AND (book_spoke = ".$spoke." OR chapter_spoke = ".$spoke.")";
    $upd3 = "UPDATE ".strtolower($bookTitle[0]).$chapter[0]." SET summary = '".$kwstring."', date_time = '".date("Y-m-d H:i:s")."', url_links= 'spoke=".$spoke."&b1=".$b1."&c1=".$c1."&b2=".$b2."&c2=".$c2."' WHERE rel_book = ".$b2." AND rel_chapter = ".$c2." AND (book_spoke = ".$spoke." OR chapter_spoke = ".$spoke.")";
    echo $upd3."<br /><br /><br />";
    //$affected_rows = $db->exec($upd3);
    $db->exec($upd3);
}
?>

[gilgalbiblewheel]

I haven’t heard from you

[droopsnoot]

You only define $dowhat inside the if() clause at the top of your code. But then later in the code, you check it’s value whether it’s been created or not:

if( isset($_GET["dowhat"]) ):
 $dowhat = $_GET["dowhat"];
endif;

creates the variable, but only if $_GET[“dowhat”] is set. Then

if($dowhat=="insindb"){

and

if($dowhat=="update"){

use the variable whether you created it or not.

I don’t think this will work either:

if(isset($dowhat)=="insindb"){
....
if(isset($dowhat)=="update"){

isset() returns either true or false, so you can’t compare that to a string value.

You could probably get rid of the “undefined” error messages by simply creating $dowhat at the top of your php code. Then figure out why it doesn’t have the value you expected.

[Gezmo]

@droopsnoot

if(isset($dowhat)==“insindb”){
…
if(isset($dowhat)==“update”){

Use the following instead:
if(isset($dowhat) AND $dowhat == “insindb”){
etc.

[gilgalbiblewheel]

why did they make this so complicated? I wanted to pass the $_GET to the php IF there is any in the url.

[Mittineague]

It isn’t complicated to do, or at least it shouldn’t be.
Handling the passed variables safely is more involved, but GET is easy enough. eg. this Google URL
https://www.google.com/search?q=php+get+variables&ie=utf-8&oe=utf-8
the GET variables would be

$_GET['q'] = "php+get+variables"
$_GET['ie'] = "utf-8"
$_GET['oe'] = "utf-8"

These get passed to “search” whatever file that might be, where they are used to produce the result page.

If you try
testpage.php

<?php
if( isset($_GET['test']) ) {
  echo "GET sent me " . $_GET['test'];
} else {
  echo "GET sent me nada";
}
?>

then go to it in your browser,
../testpage.php
you should see “nada”
If you go to
../testpage.php?test=crikey
you should see “crikey”

* important note, certain characters need to be “url encoded” so just use a single a-z word for now,

Can you at least make it that far OK?

[gilgalbiblewheel]

yeah yeah i know this.

[Mittineague]

@gilgalbiblewheel

yeah yeah i know this.

Thanks, then good so far.

If the GET variables are being properly encoded and the strings look like you expect them to. it might have something to do with the table.

Please run a “SHOW CREATE TABLE whatever-the-table-name-is here” and post that.