mysql querying problem

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
mikeeeeeeey
Forum Contributor
Posts: 130
Joined: Mon Jul 03, 2006 4:17 am
Location: Huddersfield, UK

mysql querying problem

Post 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!
User avatar
maliskoleather
Forum Contributor
Posts: 155
Joined: Tue May 15, 2007 2:19 am
Contact:

Post 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]");
User avatar
mikeeeeeeey
Forum Contributor
Posts: 130
Joined: Mon Jul 03, 2006 4:17 am
Location: Huddersfield, UK

Post 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?
User avatar
maliskoleather
Forum Contributor
Posts: 155
Joined: Tue May 15, 2007 2:19 am
Contact:

Post 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>";
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Post 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.
User avatar
mikeeeeeeey
Forum Contributor
Posts: 130
Joined: Mon Jul 03, 2006 4:17 am
Location: Huddersfield, UK

Post 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?
User avatar
maliskoleather
Forum Contributor
Posts: 155
Joined: Tue May 15, 2007 2:19 am
Contact:

Post by maliskoleather »

could you post an example of one thats being duplicated?
User avatar
mikeeeeeeey
Forum Contributor
Posts: 130
Joined: Mon Jul 03, 2006 4:17 am
Location: Huddersfield, UK

Post by mikeeeeeeey »

Code: Select all

inserted: Packaging - Mailroom and Retail Supplies

inserted: Audio Visual - Conference & Presentation
User avatar
maliskoleather
Forum Contributor
Posts: 155
Joined: Tue May 15, 2007 2:19 am
Contact:

Post 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...
User avatar
mikeeeeeeey
Forum Contributor
Posts: 130
Joined: Mon Jul 03, 2006 4:17 am
Location: Huddersfield, UK

Post by mikeeeeeeey »

thanks again for all your help, but it's still doing the same...this is extremely annoying!!! :(
User avatar
maliskoleather
Forum Contributor
Posts: 155
Joined: Tue May 15, 2007 2:19 am
Contact:

Post by maliskoleather »

could you post an update of what that code looks like now?
User avatar
maliskoleather
Forum Contributor
Posts: 155
Joined: Tue May 15, 2007 2:19 am
Contact:

Post by maliskoleather »

try

Code: Select all

if(!empty($sectionExist) && $section !== NULL){
User avatar
mikeeeeeeey
Forum Contributor
Posts: 130
Joined: Mon Jul 03, 2006 4:17 am
Location: Huddersfield, UK

Post 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...
User avatar
maliskoleather
Forum Contributor
Posts: 155
Joined: Tue May 15, 2007 2:19 am
Contact:

Post by maliskoleather »

errr. typo. i meant

Code: Select all

if(empty($sectionExist) && $section !== NULL){
User avatar
mikeeeeeeey
Forum Contributor
Posts: 130
Joined: Mon Jul 03, 2006 4:17 am
Location: Huddersfield, UK

Post 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
Post Reply