Page 1 of 2

mysql querying problem

Posted: Thu Sep 20, 2007 8:16 am
by mikeeeeeeey
Hi guys,

I'm building a system which takes a tab delimited file and inserts it into a database. However, they need to fit into 'sections' and need to be distinct, so before adding a new section I query the database to see if there's already one there.

However, this is acting weird and works for some parts but some of the sections are getting past this and being duplicated. Is there anything wrong with my code below?

Code: Select all

foreach($sections as $section){
		
			$sectionExistSql = "SELECT * FROM categories_description 
					    WHERE categories_name = '" . $section . "'";
			$sectionExistQue = mysql_query($sectionExistSql);
			$sectionExist    = mysql_num_rows($sectionExistQue);
			if($sectionExist == 0 && $section !== NULL){
				echo "<p>inserted: " . $section . "</p>";
				//KFH SECTION
				$sql   = "INSERT INTO categories (parent_id, sort_order, date_added) 
					  VALUES ('0',
						  '0',
						  '" . $date . "')";
				$query = mysql_query($sql);
		
				$sql   = "INSERT INTO categories_description (categories_id, categories_name) 
					  VALUES ('" . $cat_id . "',
						  '" . $section . "')";
				$query = mysql_query($sql);
		
				$cat_id++;
			}else{
				echo "<p>discarded: " . $section . "</p>";
			}
		}
Thanks in advance guys!

Posted: Thu Sep 20, 2007 8:18 am
by maliskoleather
you need to test if the query was successful...

Code: Select all

$sql = 'sql statement';
$foo = mysql_query($sql) or die(mysql_error()."[sql:$sql]");

Posted: Thu Sep 20, 2007 8:22 am
by mikeeeeeeey
Thanks for your reply maliskoleather, I've checked and all the queries are successful in the sense they are actually querying the database, since there are no errors being outputted with the code provided. I don't understand why some are getting past still?

Posted: Thu Sep 20, 2007 8:37 am
by maliskoleather
i doubt its where the error is coming from, but i'd change

Code: Select all

$section !== NULL
to

Code: Select all

!empty($section)
just because '' !== NULL

try doing a var_dump in the loop on $sectionExist and $section just to see what they contain...

Code: Select all

if($sectionExist == 0 && $section !== NULL){
           var_dump($sectionExist);
           var_dump($section);
           echo "<p>inserted: " . $section . "</p>";

Posted: Thu Sep 20, 2007 8:40 am
by Stryks
It's late and my eyes and brain are sore, so I just scanned over your code, but my first thought is to ...

Code: Select all

trim($section)
Assuming that $section is a string.

Posted: Thu Sep 20, 2007 8:51 am
by mikeeeeeeey
I changed the code to how you put, here's some example output:

Code: Select all

int(0) string(29) "Machine and Computer Supplies"
However, I've noticed that the ones that are being duplicated contain a hypen (-), is this just coincidence?

Posted: Thu Sep 20, 2007 9:18 am
by maliskoleather
could you post an example of one thats being duplicated?

Posted: Thu Sep 20, 2007 9:38 am
by mikeeeeeeey

Code: Select all

inserted: Packaging - Mailroom and Retail Supplies

inserted: Audio Visual - Conference & Presentation

Posted: Thu Sep 20, 2007 9:47 am
by maliskoleather
try

Code: Select all

$sectionExistSql = "SELECT * FROM categories_description WHERE categories_name = '".mysql_real_escape_string($section)."'";
the hyphen may be screwing up the query...

Posted: Thu Sep 20, 2007 10:07 am
by mikeeeeeeey
thanks again for all your help, but it's still doing the same...this is extremely annoying!!! :(

Posted: Thu Sep 20, 2007 10:19 am
by maliskoleather
could you post an update of what that code looks like now?

Posted: Thu Sep 20, 2007 10:24 am
by maliskoleather
try

Code: Select all

if(!empty($sectionExist) && $section !== NULL){

Posted: Thu Sep 20, 2007 10:30 am
by mikeeeeeeey
In response to the first post, it looks exactly the same, I tried the latest piece of code you posted and nothing has been inserted, which means nothing is getting past the if() statement...

Posted: Thu Sep 20, 2007 10:33 am
by maliskoleather
errr. typo. i meant

Code: Select all

if(empty($sectionExist) && $section !== NULL){

Posted: Thu Sep 20, 2007 10:38 am
by mikeeeeeeey
arghhhhhhhhh

still the same. here's a bigger sample...

Code: Select all

inserted: Machine and Computer Supplies

discarded: Machine and Computer Supplies

discarded: Machine and Computer Supplies

discarded: Machine and Computer Supplies

discarded: Machine and Computer Supplies

discarded: Machine and Computer Supplies

discarded: Machine and Computer Supplies

inserted: Office Essentials

inserted: Audio Visual - Conference & Presentation

inserted: Audio Visual - Conference & Presentation
these are the first 20 or so lines