I can't count these results coming from mysql!!

[jeger003]

hello,

I have a search that matches users ISBN with two databases....code is below

 

$query_search_exact_match = mysql_query("SELECT 
										   nvc_site.title, 
										   nvc_site.id,
										   nvc_site.description, 
										   nvc_site.search_text, 
										   nvc_site.image, 
										   nvc_site.date, 
										   nvc_site.price,
										   nvc_site.location_city,
										   nvc_site_ads_extra.name,
										   nvc_site_ads_extra.value,
										   nvc_site_ads_extra.classified_id
   
							FROM   nvc_site,nvc_site_ads_extra
							WHERE  name = 'ISBN%3A' AND live=1") or die(mysql_error());

 

then i take the ISBN that the user entered and match that with the isbn's in the DB

 

while ($fetch_extra = mysql_fetch_array($query_search_exact_match))
{

$value = ereg_replace( "[^0-9]", "", $fetch_extra['value'] );
$to_find_isbn = mysql_real_escape_string(ereg_replace( "[^0-9]", "",$_POST['szs']));
if($value == $to_find_isbn)
{
        echo "Match Found";
        } 
        else
        {
         echo "No Match Found";
         }
      //ELSE doesn't work here.....it displays both the if and else at the same time
}

i need it to display no match found if there was no match found.....I am soo lost right now!!

 

please help

 

 

thank you

[shlumph]

Did you properly close your else conditional?

 

Nevermind, I see you edited it

[jeger003]

haha yea some how it posted before i was done

 

but my code works fine i get everything displaying but i get both Match Found and No Match Found displaying at the same time.

 

i was going to count from sql query but that wont help since im matching the isbn later on in the While statement.

[mikosiko]

try this:

change this line:

while ($fetch_extra = mysql_fetch_array($query_search_exact_match))

 

for this

 

while ($fetch_extra = mysql_fetch_assoc($query_search_exact_match))

 

in an additional note... I did notice that your query is doing a cross join between both tables (applying the filters that you have)... is that what you want?... or you want to join the matching records?

[AbraCadaver]

haha yea some how it posted before i was done

 

but my code works fine i get everything displaying but i get both Match Found and No Match Found displaying at the same time.

 

i was going to count from sql query but that wont help since im matching the isbn later on in the While statement.

 

You're looping through all of the returned rows, so some will display match found and some will display match not found.  I'm not sure what you're doing exactly, but try this:

 

$msg = "No Match Found";
while ($fetch_extra = mysql_fetch_array($query_search_exact_match))
{
$value = ereg_replace( "[^0-9]", "", $fetch_extra['value']);
$to_find_isbn = mysql_real_escape_string(ereg_replace( "[^0-9]", "",$_POST['szs']));

if($value == $to_find_isbn)	
{
	$message = "Match Found";
	break;
} 
}
echo $message;

[jeger003]

@mikosiko

thanks for the reply but unfortunately it didnt work. it keeps display the both match found and no match found.

 

@abracadaver

wouldn't that always display No Match Found since its on the outside? I gave it a try and when there is no match it displays nothing

[jeger003]

actually using the break; never displayed anything. it stopped displaying Match Found when there was a match

[AbraCadaver]

actually using the break; never displayed anything. it stopped displaying Match Found when there was a match

 

Well, what do you want to do?  If there are 10 rows with 2 matches, then it will display match found 2 times and match not found 8 times.  What do you want?  As for my code, there was a typo, change:

 

$msg = "No Match Found";

To:

 

$message = "No Match Found";

[jeger003]

i see what you mean...that makes sense

 

what im trying do is to get it to do the search and return results

 

If there are not matches it want it to display no match found

 

if there are results i just want it to display the results

 

is there anyway to use count anywhere? or maybe before the while?

[AbraCadaver]

matches = array();
while ($fetch_extra = mysql_fetch_array($query_search_exact_match))
{
$value = ereg_replace( "[^0-9]", "", $fetch_extra['value'] );
$to_find_isbn = mysql_real_escape_string(ereg_replace( "[^0-9]", "",$_POST['szs']));

if($value == $to_find_isbn)	
{
	$matches[] = $value;
} 
}
if(!empty($matches)) {
echo count($matches) . ' matches found: ' . implode(', ', $matches);
} else {
echo "No matches found!";
}

[jeger003]

thanks for the reply abracadaver

 

i used what you gave me and here is what happns.

 

it says there are 1000 matches..here's why i think it does this

 

my db is populated already with isbn numbers

 

so what i think is happening is its running the sql query and anything that has isbn filled...its calling that a match

 

i only tell it to display the one that matches the users isbn entered

 

so when i run the query it tells me there are 1000 matches....because 1000 isbn fields are filled but 1 matches the ISBN entered in the search

 

im doing this all wrong

 

I think I may need to change my sql query.

 

is there a way to match isbn before the while statement? i dont know how to since im using 2 tables

[AbraCadaver]

I'm confused now, you should be able to do this all in the query.  What db field holds the value that you want to match?  What is the format?  What does the user enter?

[jeger003]

ok so i have a search on my homepage with the option to search with ISBN

 

this is the structure of my db

 

I am using TWO tables for the search

nvc_site and nvc_site_ads_extra

 

nvc_site holds all the classified ads - this is used to display the match

 

nvc_site_ads_extra holds the ISBN this is used for the search

 

so nvc_site_ads_extra has two fields name and value. name has ISBN in it and value is the ISBN NUMBER

 

my query selects these two tables and takes ISBN in field name and ISBN number in field value

 

then matches that with users ISBN entered

 

thats where [if($value == $to_find_isbn)] comes in....if value (which isbn number stored in db) matches $to_find_isbn (which users entered isbn ie $_POST['szs'])

 

then it displays the listings information - and gets title, image etc from nvc_site (WHERE id from both tables match)

 

which gives me the correct listing

 

does that make sense?

 

so the query is returning all isbn numbers but showing only the one that matches $_POST['szs'] thats why there is always a match

[mikosiko]

try/adjust this... replacing "nvc_site_ads_extra.???" with the right column that join both tables

 

$userISBNvalue = mysql_real_escape_string($_POST['szs']);

$query_search_exact_match = mysql_query("SELECT nvc_site.title, 
									nvc_site.id,
									nvc_site.description, 
									nvc_site.search_text, 
									nvc_site.image, 
									nvc_site.date, 
									nvc_site.price,
									nvc_site.location_city,
									nvc_site_ads_extra.name,
									nvc_site_ads_extra.value,
									nvc_site_ads_extra.classified_id
							  FROM nvc_site_ads_extra
							            JOIN nvc_site ON nvc_site.id = nvc_site_ads_extra.???
							  WHERE  nvc_site_ads_extra.name = 'ISBN%3A'
                                                                        AND  nvc_site_ads_extra.value = $userISBNvalue 
                                                                        AND live=1") or die(mysql_error());

$num_rows = mysql_num_rows($result);
if ($num_rows > 0) {
   echo "Match Found";
   // Loop the resultset and do whatever you want with the data
   while ($fetch_extra = mysql_fetch_assoc($query_search_exact_match))
   {
             // display the data or do what your need
   }
} else {
   echo "No Match found";
} 

[jeger003]

oh man you are goodd!!!!

 

it works great.....but here is where i am stuck

 

when my users enter isbn numbers that get stored in the value field  - they include letters and dashes

 

for example some are stored like this

 

ISBN:97544444444 (example)

 

so when a users enters 97544444444 in the search it wont find it because of the letters ISBN: so what i want to do is  use

 

ereg_replace( "[^0-9]", "", )) - but how can i do this while in the database?

 

so in order to match

 

users ISBN search 97544444444

 

with ISBN's stored in the ISBN:97544444444

 

it needs to remove ISBN:

 

any idea?

 

this was the reason why i took the fields out first then matching them up - because i needed to clean it up from the db

 

thanks again guys for all the help.......i really hope we can get this one solved!

[mikosiko]

I have not played with it but you can try with MYSQL REGEXP

 

http://dev.mysql.com/doc/refman/5.1/en/regexp.html#operator_regexp

 

 

[jeger003]

this seems like it could work but i dont see any examples on how to get it working in the WHERE of an sql query.

 

Is there a way to get REGEXP to match just numbers in WHERE

 

for example

 

SELECT * FROM table WHERE isbn REGEXP $users_isbn

 

is there any way to match numbers only in a sql query?

 

 

i'd appreciate any help

 

thank you guys

 

 

[jeger003]

is there a way to get this to work to match only numbers?

 

$query_search_exact_match = mysql_query("SELECT 
							nvc_classifieds.title, 
							 nvc_classifieds.id,
							nvc_classifieds.description, 
							 nvc_classifieds.search_text, 
							 nvc_classifieds.image, 
							nvc_classifieds.date, 
							nvc_classifieds.price,
							nvc_classifieds.location_city,
							nvc_classifieds_ads_extra.name,
							nvc_classifieds_ads_extra.value,
							nvc_classifieds_ads_extra.classified_id
   
									FROM   	nvc_classifieds_ads_extra
									JOIN 	nvc_classifieds 
									ON 		nvc_classifieds.id = nvc_classifieds_ads_extra.classified_id
									WHERE  	nvc_classifieds_ads_extra.name = 'ISBN%3A'
									AND 	live=1 
									AND  	(nvc_classifieds_ads_extra.value 
									REGEXP '[0-9] $search_isbn') 
									") or die(mysql_error());

 

using REGEXP - i want it to match only numbers but It does not

 

anyone have any ideas?