• 0

Is a string present in a MySQL database?


Question

OK, I know this is a simple thing but I am *VERY* new at MySQL, my second day at it in fact. I'm going to buy a book tomorrow but in the mean time, I am hoping someone could answer a simple question.

This is what I want to do; if the name "Ash" is present in the "recipient" column, then I want it to return true, else return false.

Here is what my Messages table may look like:

MESSAGES

+------+--------+-------------+----------------+

|??ID??|??FROM??|??RECIPIENT??|??MESSAGE???????|

+------+--------+-------------+----------------+

|???1??|??Jim???|??Lisa???????|??I?love?you????|

+------+--------+-------------+----------------+

|???2??|??Lisa??|??Jim????????|??F.?Off!???????|

+------+--------+-------------+----------------+

|???3??|??Jim???|??Lisa???????|??Whatever!?????|

+------+--------+-------------+----------------+

|???4??|??Will??|??Ash????????|??How?are?you???|

+------+--------+-------------+----------------+

|???5??|??Ash???|??Andy???????|??Hi????????????|

+------+--------+-------------+----------------+

|???6??|??Andy??|??Ash????????|??Hello?Ash?????|

+------+--------+-------------+----------------+

|???7??|??Donna?|??Ash????????|??Word?Up?Dog???|

+------+--------+-------------+----------------+

As you can see from the example above, "Ash" has received 3 messages and "Donna" has received none. I want to use this as part of a Private messaging system I am trying to build on a test site. The idea is that members can leave messages for other members, and when someone logs in, i.e. "Ash", he sees that there is a message waiting for him.

I can use the following code to get a list of everything in the database, but I need a test to see if "Ash" has a message waiting for him.

<?php
$connection = mysql_connect ("localhost", "root", "whatever");
if ($connection == false){
 ?echo mysql_errno().": ".mysql_error()."<BR>";
 ?exit;
}

$query = "select * from message";
$result = mysql_db_query ("messenger", $query);

if ($result){
 ?echo "<TABLE Border=1>";
 ?echo "<TR><TD><B>Sender</B></TD><TD><B>Recipient</B></TD><TD><B>Message</B></TD></TR>";
 ?$numOfRows = mysql_num_rows ($result);
 ?for ($i = 0; $i < $numOfRows; $i++){
 ? ?$sender = mysql_result ($result, $i, "sender");
 ? ?$recipient = mysql_result ($result, $i, "recipient");
 ? ?$message = mysql_result ($result, $i, "message");
 ? ?echo "<TR><TD>$sender</TD><TD>$recipient</TD><TD>$message</TD></TR>";
 ?}
 ?echo "</TABLE>";
}
else{
 ?echo mysql_errno().": ".mysql_error()."<BR>";
}
mysql_close ();
?>

Thanks.

Link to comment
Share on other sites

7 answers to this question

Recommended Posts

  • 0

This may or may not work. I'm new at SQL too :)

$query = "select * from MESSAGES where RECIPIENT='Ash'";

That should query all the rows where Recipient contains Ash.

I just started reading a book called "MySQL/PHP Database Applications by Jay Greenspan and Brad Bulger". It has been quite helpful so far. It sells for about $50, but you might be able to find it on kazaa.

Edited by Clone5k
Link to comment
Share on other sites

  • 0

Can you show me how to implament this?

I've used the following but it does not work:

echo "<HR>";
$query = "select * from MESSAGES where RECIPIENT='Ash'";
$result = mysql_db_query ("messenger", $query);
$whatever = mysql_result ($result)
echo $whatever;

Link to comment
Share on other sites

  • 0

Well, if I was going to print out the results from the query I would do something like:

mysql_connect() or die ("Problem connecting to DataBase");

$query = "select * from MESSAGES where RECIPIENT='Ash'";
$result = mysql_db_query("messenger", $query);

while ($row = mysql_fetch_array($result)) {
	$id = $row["ID"];
	$from = $row["FROM"];
	$recipient = $row["RECIPIENT"];
	$message = $row["MESSAGE"];

echo "From: $from <br>
   Recipient: $recipient <br>
   Message: $message <br>
   <br>";
}

mysql_fetch_array($result) assigns the current row as an array to $row. You can access the values by using $whatever = $row["column_name_goes_here"];

The 'while' loop will keep going until there aren't any rows left from the query result.

So in my example it would print out something like:

From: Will

Recipient: Ash

Message: How are you?

From: Andy

Recipient: Ash

Message: Hello Ash

From: Donna

Recipient: Ash

Message: Word Up Dog?

If you wanted to show something like "You have '3' new messages." I guess you could count how many rows the query returned.

Hope this helped a little.

Link to comment
Share on other sites

  • 0

Thank you, that does help.

I have use the code below, is it the best way to do this?

<?php
$db = mysql_connect("localhost", "root", "whatever");
mysql_select_db("messenger",$db);
$result = mysql_query("SELECT * FROM message",$db);

$PersonalMessageCount = 0;

if ($myrow = mysql_fetch_array($result)) {
 do {
 ?if ($myrow["recipient"] == "Ash"){
 ? $PersonalMessageCount++;
 ?}
 } while ($myrow = mysql_fetch_array($result));
}

if ($PersonalMessageCount!=0){
 ? echo "You have <B>".$PersonalMessageCount."</B> peronal messages";
}else{
 ? echo "You don't have any personal messages";
}
?>

Link to comment
Share on other sites

  • 0

that's sorta it, but you can do it in a more efficient algorithm:

<?php
$db = mysql_connect("localhost", "root", "whatever");
mysql_select_db("messenger",$db);
$result = mysql_query("SELECT COUNT(*) AS pmcount FROM message WHERE recipient = '$username'",$db);
$row = mysql_fetch_array( $result );

$PersonalMessageCount = $row['pmcount'];

if ($PersonalMessageCount!=0){
  echo "You have <B>".$PersonalMessageCount."</B> peronal messages";
}else{
  echo "You don't have any personal messages";
}
?>

The COUNT(*) will just return the number of rows in the table that are returned (based on the WHERE statement(s) ). I used AS pmcount to give the column a more meaningful name too. I also assumed $username is your input, so you can just set $username to the person you want and reuse the algorithm.

It's all about relying on SQL to do some of the data minig for you, as it'll be faster at the job :)

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.