Why cant this insert to database?

[JJohnsenDK]

Hey

 

Why doesnt this code not insert the data into the database?

 

<?php
// Goes through all the words in url/website and insert them into the database
function _harvest($url)
{
	   if($this->_checkURL($url))
	   {
		      echo "URL is not valid: $url.";
	   }elseif($data = $this->_getData($url)){
		      $words = preg_split('/[\s,.]+/', $data);
		      array_walk($words, array($this, '_prune'), $words);
		      sort($words);
		      $url_id = $this->_db->getone("SELECT id FROM urls WHERE url = '$url'");
		      if($url_id)
		      {
			         $this->_db->query("DELETE FROM keywords WHERE url_id = '$url_id'");
		      }else{
			         $this->_db->query("INSERT INTO urls SET url = '$url'");
			         $url_id = mysql_insert_id();
		      }

		      $values = "($url_id, '$words[0]')";
		      $numwords = count($words);
		      for($i=1; $i < $numwords; $i++)
		      {
			         $values .= ", ($url_id, '$words[$i]')";
		      }

		      $this->_db->query("INSERT INTO keywords VALUES $values");
	   }   
}
?>

 

i get this error:

Invalid query: 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 '' at line 1

 

*the $this->_db->query is holding mysql_query() in ohter class.

[steve448]

This:

<?php
$this->_db->query("INSERT INTO urls SET url = '$url'");
?>

Should be something like:

<?php
$this->_db->query("INSERT INTO urls (url) VALUES ('$url')");
?>

 

[JJohnsenDK]

sorry that i didnt point out the section where the code goes wrong.

 

Its this part:

 

$this->_db->query("INSERT INTO keywords VALUES $values");

 

It does insert it into the database.

 

What you said steve448 works fine already.

[Ninjakreborn]

You have to specify row names, like this.

$this->_db->query("INSERT INTO keywords (fieldname) VALUES ($values)");

 

[JJohnsenDK]

sry for the late respond, but i passed out last night...

 

now i tried this:

 

$this->_db->query("INSERT INTO keywords (url_id, keyword) VALUES $values");

 

but it still gives me the same error.

 

anyone who got any ideas for this?

[steve448]

If you echo out $values before the insert then you should see your problem.

 

Your insert statement should be in this format:

<?php
$sql = "INSERT INTO keywords (url_id, keyword) VALUES ('$url_id', '$keyword')";
?>

 

When you are building your $values string it is all jumbled up.

[JJohnsenDK]

well, yes it is jumbled up with a lot of values of the insert query. So it contains for exampel:

 

(1, 'Skotland'), (1, 'Skotland'), (1, 'Skotsk'), (1, 'Slagskibe')

 

and this is what $values prints out when i echo it out. So i cant see the why mysql is getting me the Invalid query error because my insert query would look like this:

 

$this->_db->query("INSERT INTO keywords (url_id, keyword) VALUES (1, 'Skotland'), (1, 'Skotland'), (1, 'Skotsk'), (1, 'Slagskibe')");

 

why is that wrong?

 

[JJohnsenDK]

when i put this in my script:

 

$this->_db->query("INSERT INTO keywords (url_id, keyword) VALUES (1, 'Skotland'), (1, 'Skotland'), (1, 'Skotsk'), (1, 'Slagskibe')");

 

it inserts it into the database??? i simply cant see why i cant use that $values variable for this? when it contains the same information?

[steve448]

You are trying to put 8 values into 2 fields of your database.

 

I think for what you are trying to do you are going to need to do do more than one insert query.

 

So if you change this:

<?php
		      $numwords = count($words);
		      for($i=1; $i < $numwords; $i++)
		      {
			         $values .= ", ($url_id, '$words[$i]')";
		      }

		      $this->_db->query("INSERT INTO keywords VALUES $values");
?>

To something like:

<?php
$numwords = count($words);
		      for($i=1; $i < $numwords; $i++)
		      {
                               $keyword = $words[$i];				
         $this->_db->query("INSERT INTO keywords (url_id, keyword) VALUES ('$url_id', '$keyword')");	     
   }
?>

 

That is assuming that you want the url_id to always be the same, which it is at the moment.

 

Are you sure that url_id is not an auto_increment field?

 

Then you would want:

<?php
$numwords = count($words);
		      for($i=1; $i < $numwords; $i++)
		      {
                               $keyword = $words[$i];				
         $this->_db->query("INSERT INTO keywords (keyword) VALUES ('$keyword')");	     
   }
?>

 

[JJohnsenDK]

allright i will this... yes im sure that it isnt auto_increment... it just store the id from a url table so i can point the keywords to a specific url...

[JJohnsenDK]

crap... this gives me the same error as before. I am now using this:

 

<?php
$numwords = count($words);
		for($i=1; $i < $numwords; $i++)
		{
                   $keyword = $words[$i];				
         		   $this->_db->query("INSERT INTO keywords (url_id, keyword) VALUES ('$url_id', '$keyword')");	     
   			}
?>

[steve448]

Sorry, scrap my last post then, I'm an idiot!

 

This should work

<?php
$values = "($url_id, '$words[0]')";
		      $numwords = count($words);
		      for($i=1; $i < $numwords; $i++)
		      {
			         $values .= ", ('$url_id', '$words[$i]')";
		      }

		      $this->_db->query("INSERT INTO keywords VALUES $values");
?>

Need to have the quotes around $url_id

[JJohnsenDK]

im sry to say this but it doesnt work either... same error... this is really a pain in the ass!... its real nice you are helping, tho i really appreciate that...

 

i copy/paste your code, but the same thing

[steve448]

Post your entire code as you have it now so I can see where we're up to

[JJohnsenDK]

<?php

require('db.php');

 

class Harvest_Keywords

{

 

// Private variables

var $_db;

var $_urlarray;

var $_stopwords = array('and', 'but', 'are', 'the');

var $_allowwords = array('c++', 'ado', 'vb');

 

// Construtor

function Harvest_Keywords($urls)

{

$this->_db = new DB_Class('test', 'root', '');

$this->_urlarray = trim($urls);

$this->_urlarray = explode("/n", $this->_urlarray);

}

 

// prune function - checks the words

function _prune($item, $array, $key)

{

$item = strtolower($item);

if(((preg_match("/[^a-z0-9'\?!-]/", $item)) || (strlen($item) > 3) || (in_array($item, $this->_stopwords))) && (!in_array($item, $this->_allowwords)))

{

unset($array[$key]);

}else{

$item = addslashes(preg_match("/[^a-z0-9'-]/", $item));

}

}

 

// Check the url to see if it is correct

function _checkURL($url)

{

return preg_match ("/http:\/\/(.*)\.(.*)/i", $url);

}

 

// Gets the information from the URL with fopen and fread

function _getData($url)

{

$filehandel = fopen($url, 'r');

if(!$filehandel)

{

echo "Could not read the URL.";

$return = FALSE;

}else{

$data = fread($filehandel, 25000);

$close = fclose($filehandel);

$data = strip_tags($data);

$data = str_replace('&nbsp', ' ', $data);

$return = $data;

}

 

return $return;

}

 

// Goes through all the words in url/website and insert them into the database

function _harvest($url)

{

if($this->_checkURL($url))

{

echo "URL is not valid: $url.";

}elseif($data = $this->_getData($url)){

$words = preg_split('/[\s,.]+/', $data);

array_walk($words, array($this, '_prune'), $words);

sort($words);

$url_id = $this->_db->getone("SELECT id FROM urls WHERE url = '$url'");

if($url_id)

{

$this->_db->query("DELETE FROM keywords WHERE url_id = '$url_id'");

}else{

$this->_db->query("INSERT INTO urls SET url = '$url'");

$url_id = mysql_insert_id();

}

 

$values = "($url_id, '$words[0]')";

      $numwords = count($words);

      for($i=1; $i < $numwords; $i++)

      {

        $values .= ", ('$url_id', '$words[$i]')";

      }

}

}

 

// Get the script running by calling harvest() function as many times as there are urls

function process()

{

foreach($this->_urlarray as $url)

{

$this->_harvest($url);

}

}

}

 

?>

 

dont know how to get the colors on without putting it the in

 tags... if i put it in the [code] tags it remove all my tabs and the code is unreadable.

[/code]

[steve448]

Change:

<?php
$values = "($url_id, '$words[0]')";
?>

 

To:

<?php
$values = "('$url_id', '$words[0]')";
?>

 

Missed of the quotes for the $url_id at the beginning of $values

[JJohnsenDK]

nope... didnt work

 

the strange thing about this is that i can echo the values, copy them into the insert query instead of $values, then it works fine  ???

[steve448]

Are you still getting any error because you don't currently have the insert in your script?

 

<?php
         $values = "('$url_id', '$words[0]')";
         
               $numwords = count($words);
         
               for($i=1; $i < $numwords; $i++)
         
               {
         
                     $values .= ", ('$url_id', '$words[$i]')";
         
               }
               //Is this part still in your script?      
               $this->_db->query("INSERT INTO keywords (url_id, keyword) VALUES $values");
?>
[code]

[/code]

[JJohnsenDK]

yes im still getting the same error:

 

Invalid query: 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 '' at line 1

 

i just forgot to do ctrl + z before posting the code 

[steve448]

Ok, think this is going to have to be my last attempt because if this doesn't work then I'm lost!

 

But first check that you do have 2 columns in your database and they are called exactly url_id and keyword

<?php
               $numwords = count($words);
         
               for($i=0; $i < $numwords; $i++)
         
               {
         
               $keyword = $words[$i];
               
               $this->_db->query("INSERT INTO keywords (url_id, keyword) VALUES ('$url_id', '$keyword')");
         
               }
?>

[JJohnsenDK]

still the same  :'( ... but thanks alot for all your eforts! i really appreciate that you took all this time to help out

 

hopefully some new einstein checks in to this post later

[JJohnsenDK]

anyone who can help me out on this?