Is something wrong with my prepared statements

PHP
1

I cant seem to figure out how to correct this error. i’ve tried multiple ways but to no avail.

function.php

 function updateData() 
    {
        // Base url to append to extracted links
        $baseUrl = 'http://www.sharenet.co.za';
    
        // Starting point to begin extraction
        $url = 'http://www.sharenet.co.za/v3/q_sharelookup.php';
    
        // From dbconnect.php
        global $pdo;
    
        // Calling function curlGet and storing returned results in $sharenetData variable
        $sharenetData = curlGet($url);    
    
        // Instantiating new XPath DOM object
        $sharenetDataXpath = returnXPathObject($sharenetData);
    
        // Querying for all href links on url
        $links = $sharenetDataXpath->query('(//table[@class="dataTable"]//table)[1]//a/@href');
    
        for ($i=0; $i < $links->length; $i++) 
        { 
            // Append baseUrl
            $url = $baseUrl . $links->item($i)->nodeValue;
    
            // Replace all occurrences of ' ' with '%20'
            $url = str_replace(' ', '%20', $url);
    
            // Calling function curlGet and storing returned results in $sharenetData variable
            $sharenetData = curlGet($url);    
    
            // Instantiating new XPath DOM object
            $sharenetDataXpath = returnXPathObject($sharenetData);
    
            // Querying for company short name
            $shortnames = $sharenetDataXpath->query('//table[@class="dataTable"]//a/text()');
    
            
            if ($shortnames->length > 0) 
            {
                $x = 2;
    
                for ($i=0; $i < $shortnames->length; $i++) 
                { 
                    // extract all shortcodes
                    $shortcode = $sharenetDataXpath->query('(//table[@class="dataTable"]//tr/td[@class="dataCell"]/text())[' . $x . ']');
    
                    // Remove unwanted characters
                    $name = substr($shortnames->item($i)->nodeValue, 2,-2);
    
                    $code = $shortcode->item(0)->nodeValue;
                    
                    // Increment $x by 3 during loop
                    $x += 3;
    
                    try
                    {
                        $sql = "INSERT INTO stock SET shortname = :name WHERE shortcode = :code ";
    
                        $s = $pdo->prepare($sql);
    
                        $s->bindValue(':name', $name);
                        $s->bindValue(':code', $code);
    
                        $s->execute();
                    }
                    catch (PDOException $e)
                    {
                        // Show exception error
                        echo 'Error: ' . $e->getMessage();    
                        exit();
                    }
                }
            }
        }
    }

index.php

<?php 
    
    error_reporting(E_ALL);
    ini_set('display_errors', 1);
    
    require_once 'functions.php';
    
    updateData();
    ?>

I get the following error:

Error: SQLSTATE[42000]: Syntax error or access violation: 1064 You have 
an error in your SQL syntax; check the manual that corresponds to your 
MySQL server version for the right syntax to use near 'WHERE shortcode =
 'OAON'' at line 1

Any help?

2

What is the value of $code ?

I think that’s where the problem lies.

3

@Mittineague The value of code is the following:

OAON
OAO
CME
SCLN
MNK
SCL
SOLBE1
SOL

it has been extracted from site using the following:

// extract all shortcodes

$shortcode = $sharenetDataXpath->query('(//table[@class="dataTable"]//tr/td[@class="dataCell"]/text())[' . $x . ']');

then saved in $code through the following:

$code = $shortcode->item(0)->nodeValue;
4

@Mittineague within a for loop hence the $x increment

5

I have also tried to bind the parameters explicitly with bindParam(), still same error.

6

The output of var_dump($s->ErrorInfo());

array(3) {[0]=>string(0) "" [1]=>NULL [2]=>NULL}
7

DOM parsing nested tables is messy. Don’t they have a better API?

8

Unfortunately not.

9

I’m no expert, but if you’re updating shouldn’t you be using UPDATE in the query?

10

Good catch. The query is a bit mixed up.

If it is an INSERT there should not be a WHERE
http://dev.mysql.com/doc/refman/5.6/en/insert.html

11

Wow thanks man for pointing that out, code works perfect now, no more go sleeping in the wee morning hours for me, it really screws mental capacity.

You know i was struggling with this for a day now, you wont believe how relieve i feel now.

Again thanks.

Ps. People, for us developers, a good night’s rest is benificial, the problem might stare you right in the face. Go sleep guys, a foggy brain might get you fired :wink:

12

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.