LIKE querry not quite working..

Skull

first off here's the code that I'm using...it's not quite perfect but meh(if anyone has any tips on how to make the code better than that would be kool aswell but anyway..)

echo "<table class='bars'>
		<tr colspan='2'>
			<td>Name</td>
			<td>Last Post</td>
		</tr>";
//select everyone from the active roster
$SQL = "SELECT * FROM rosterdata WHERE ACTIVE='y' ORDER BY ALTNAME";
$result = mysql_query($SQL) or die(mysql_error());
while($row = mysql_fetch_array($result)){ // start mysql stuff

$name = $row['ALTNAME'];
$joindate = $row['JOINDATE'];
echo "
	<tr>
			<td bgcolor='black'>$name</td>";
$SQL2 = "SELECT * FROM boards WHERE BOARD='2' AND NAME LIKE '%$name%' OR BOARD='3' AND NAME LIKE '%$name%'
GROUP BY NAME";
$result2 = mysql_query($SQL2) or die(mysql_error());
while($row2 = mysql_fetch_array($result2)){ 
$board_name = $row2['NAME'];
$date = $row2['DATE'];
// time to convert the date
$date_2 = substr($date,0,-8); 
$date_2 = explode(",",$date_2);
$date_2 = $date_2[1];
$date_2 = explode(" ",$date_2);
$date_2[3] = eregi_replace("2003","03",$date_2[3]);
$date_2[2] = eregi_replace("January","01",$date_2[2]);
$date_2[2] = eregi_replace("February","02",$date_2[2]);
$date_2[2] = eregi_replace("March","03",$date_2[2]);
$date_2[2] = eregi_replace("April","04",$date_2[2]);
$date_2[2] = eregi_replace("May","05",$date_2[2]);
$date_2[2] = eregi_replace("June","06",$date_2[2]);
$date_2[2] = eregi_replace("July","07",$date_2[2]);
$date_2[2] = eregi_replace("August","08",$date_2[2]);
$date_2[2] = eregi_replace("September","09",$date_2[2]);
$date_2[2] = eregi_replace("October","10",$date_2[2]);
$date_2[2] = eregi_replace("November","11",$date_2[2]);
$date_2[2] = eregi_replace("December","12",$date_2[2]);
$date_2 = "$date_2[3]$date_2[2]$date_2[1]";
			if($date_2 > $joindate){ 
echo "			<td bgcolor='black'>$date</td>";
}else{
echo "			<td bgcolor='black'>No Post Since Joining</td>";
}
echo "	</tr>";
}
if($name !== "$board_name"){
echo "			<td bgcolor='black'>No Post Since Joining</td>";
}
} // end mysql stuff
echo "</table>";

ok so basically if I have a name saying 'Stone Cold Steve Austin' in the rosterdata table, and I have the name as 'Stone Cold' in the boards table it will output 'No Post Since Joining'

so basically is there anyway to get around this so that it will recognise 'Stone Cold' as being 'Stone Cold Steve Austin' or 'Randy Orton & Sean O'Haire' as 'Randy Orton' and 'Sean O'Haire'

if it doesn't make much sence please reply and i'll try to explain it more

thanks in advance

barand

Try

$SQL2 = "SELECT * FROM boards WHERE NAME LIKE '%$name%' AND BOARD IN ('2' , '3') ORDER BY NAME";

barand

P.S What date format are you starting with and how do you want it to be formatted? There seems to be an awful lot of code there to convert a date.

vponz

The query you're ¡ssuing I think it is wrong:

$SQL2 = "SELECT * FROM boards WHERE BOARD='2' AND NAME LIKE '%$name%' OR BOARD='3' AND NAME LIKE '%$name%' "

Say name is "Victor"; This query returns all posts made by "%Victor%" and every message in Board 3.
You should run this query (with parenthesis)
$SQL2 = "SELECT * FROM boards WHERE (BOARD='2' AND NAME LIKE '%$name%') OR (BOARD='3' AND NAME LIKE '%$name%')

Hope it helps you

Skull

barand

the date I am using for the rosterdata date is

date("ymd") - 000000

and the date i am using in the boards is this

date("l, d F Y h:i a",time() + $timeadjust)

and it doesn't work, still doesn't find some made by some names

vponz

doesn't work either 🙁 , same reason as above

barand

The trouble is that LIKE is for finding data that contains the search string and not data contained IN the search string. You will probably have to break down the search string into separate words and do a search like

$SQL2 = "SELECT * FROM boards
WHERE ((NAME LIKE '%Stone%' )
OR (name LIKE '%Cold%' )
OR (name LIKE '%Steve%' )
OR (name LIKE '%Austin%' ))
AND BOARD IN ('2' , '3')
ORDER BY NAME";

To convert your date, have you tried

$date = $row2['DATE']; 
$date2 = date('ymd' , strtotime($date));

barand

PS You could maybe experiment with

$SQL2 = "SELECT * FROM boards
WHERE SOUNDEX(NAME) = " . soundex($name)
. " AND BOARD IN ('2' , '3') ORDER BY NAME";

Skull

thanks for that date tip, that should save me alot of time in the future 😃

I looked up the soundex function to see what it does and it seemed like it would work,so i put it in and put in the '' around " . soundex($name)
. " and experimented a bit with it but still no luck 🙁

the only other thing I can think of doing is using explode() to seperate each name where there is a space, then do this

$SQL2 = "SELECT * FROM boards
WHERE ((NAME LIKE '%Stone%' )
OR (name LIKE '%Cold%' )
OR (name LIKE '%Steve%' )
OR (name LIKE '%Austin%' ))
AND BOARD IN ('2' , '3')
ORDER BY NAME";

I don't know how I would go about having it know how much

OR (name LIKE '%Steve%' )

it should have..any ideas on this or any other way to do it?

barand

Look up fulltext in MySql manual.

SELECT * WHERE MATCH(name) AGAINST ('Stone Cold Steve Austin')

Skull

already tried that but since the coloum's are varchar and not fulltext it won't work.

would changing it to fulltext make a difference or would it just act the same?

barand

Varchar is fine - its the index that is FULLTEXT, not the field

mysql_query ("ALTER TABLE boards ADD FULLTEXT idx_name (name)");

Skull

finally! it worked, thanks alot!

but now there's another problem 🙁

all the way through the table theres stuff like (the | indicating its a new coloum)

AJ Styles | No Post Since Joining
Al Snow | Thursday, 06 November 2003 10:25 pm
Batista | No Post Since Joining
Brock Lesner | No Post Since Joining
No Post Since Joining
Tuesday, 04 November 2003 03:27 am
No Post Since Joining
Chavo Guerrero | Thursday, 16 October 2003 05:29 pm

this mucks up the look of the table and gives me information that I don't need or know what its for....any ideas?

the querry im using is below

$SQL2 = "SELECT * FROM boards WHERE MATCH(NAME) AGAINST ('$name') AND BOARD IN ('2' , '3') GROUP BY NAME";

barand

Try filtering out blank names

$SQL2 = "SELECT * FROM boards WHERE name <> '' AND MATCH(NAME) AGAINST ('$name') AND BOARD IN ('2' , '3') GROUP BY NAME";

Skull

ok, well I seem to have figured out one of the problems anyway

if($name !== "$board_name"){
echo "			<td bgcolor='black'>No Post Since Joining</td>";
}

that is why I keep getting the extra coloums, cause not all of the $name variables are exactely the same as the $board_name one's...is there anything I can do here to make it if it is a part of it that is the same or something?

thanks again for all your help