generate query and execute with php

[raxo]

I am trying to generate a query and execute with php. The sql-query is also saved in a file. And in case a field in the table exists with some specific attributes, the query updates instead of insert.

Everything goes well until the query is executed.

 

<?php
function scrapeSidaLevel1a ($url)
{
        // Here i call on a function that scrapes a site, 
// returns scraped information in array. 
        $cScrape2  =	 new cScrape2;
$html = str_get_html($cScrape2->scrapeSidaLevel1($url));

$a=0;

//Connectar till databasen
$username="admin";
$database="database1";
$password="xxxxx";

mysql_connect(localhost, $username, $password);
@mysql_select_db($database) or die ("sorry, no database, try again later");

$fh=fopen("mapp/level_1.sql", w);

foreach($html->find('a') as $element)
{
	$a++;

	$query = mysql_query("SELECT * FROM `tbl_table` WHERE `level`=1 AND `text`='" .mysql_real_escape_string($element->plaintext)."' LIMIT 0,1"); 

	// if there is a field in table with this level and have the same name, the link and id is updated. 
                // else it get inserted. 
	if($query)
	{
		$output.= "UPDATE
				`tbl_table`
				SET
				`url`='http://www.sida.com" .mysql_real_escape_string($element->href) . "',
				`id`=" .$a. "
				WHERE `level`=1 AND `text`='" .mysql_real_escape_string($element->plaintext)."';";
	}
	else
	{
		$output.= "INSERT INTO
					`tbl_table`
					(
					`text`,
					`url`,
					`id`, 
					`level`
					)
					VALUES 
					(
					'" .mysql_real_escape_string($element->plaintext). "',
					'http://www.sida.com" .mysql_real_escape_string($element->href) . "',
					" .mysql_real_escape_string($a). ", 
					1
					)
					;";
	}
}

echo "Run query: <br>" .$output. "<p>";

// if querry fails, echo the error and die. 
if (!mysql_query($output))
{
	die("Error: " . mysql_error() . "<p>DIIIIEEEEE!!!!");
        }
	  
mysql_close();

fwrite($fh, $output);
fclose($fh);

$html->clear();
}
?>

 

functions output:

Run query:

UPDATE `tbl_table` SET `url`='http://www.sida.com/page/model_chp.asp', `id`=1 WHERE `level`=1 AND `text`='Compaq Parts Directory';UPDATE `tbl_table` SET `url`='http://www.sida.com/page/dell.html', `id`=2 WHERE `level`=1 AND `text`='Dell Parts Directory';UPDATE `tbl_table` SET `url`='http://www.sida.com/page/model_chp.asp', `id`=3 WHERE `level`=1 AND `text`='Hp Parts Directory';UPDATE `tbl_table` SET `url`='http://www.sida.com/page/ibm.html', `id`=4 WHERE `level`=1 AND `text`='IBM Parts Directory';UPDATE `tbl_table` SET `url`='http://www.sida.com/page/toshiba.html', `id`=5 WHERE `level`=1 AND `text`='Toshiba Parts Directory';UPDATE `tbl_table` SET `url`='http://www.sida.com/manufacturer/all.html', `id`=6 WHERE `level`=1 AND `text`='View All Manufacturers';

 

Error: 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 ';UPDATE `tbl_table` SET `url`='http://www.sida.co' at line 6

 

DIIIIEEEEE!!!!

 

the weird thing is: if i put paste the sql-query from output in phpMyAdmin it runs without an error. Why? :S

 

 

thanks in advance!

[PFMaBiSmAd]

mysql_query() does not support multiple queries separated by ; because too many people using php were not validating incoming data to prevent sql injection.

 

You will need to execute each query statement separately in a loop.

[raxo]

First of all: thanks!!!! 

 

But i have a question... is it possible to write it to a sql file and import it somehow to phpMyAdmin automatically?

It would be the same effect like my code if it had worked, right? ^.-

[PFMaBiSmAd]

If you are willing or able to switch to mysqli it has a mysqli_multi_query() function that does do what you want.