PHP + MySQL syntax issues

[darkeye]

Hello,

 

I am having some trouble getting the below code to work.

 

...[stuff above here]
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM responses WHERE 'condition' LIKE '".$_POST['msg']."%' ORDER BY RAND() LIMIT 1";
$result=mysql_query($query);
$print=mysql_fetch_assoc($result);
echo $print;
mysql_close();
echo $result; 

 

All of the variables in the the question have been defined elsewhere... ($username, $password, etc.)

So that is not the issue.

 

The problem is that all that is returned is "Resource ID #3" which is from $result, (mysql query).

However, If I enter in 'test', or 'tes' for $_POST['msg'], I know for a fact that those are in my table, but nothing is returned with $print.

 

What Am I doing wrong?

 

[mrMarcus]

for starters, don't use single-quotes around field names:

 

... WHERE 'condition' LIKE ...

 

use back-ticks (they are not required, but since MySQL has some reserved names, if you happened to name one of your fields the same as a reserve name, you would get an error.  using the ` as a delimiter will prevent that:

 

... WHERE `condition` LIKE ...

 

now, you can't echo out the resource, you need to assign it and go from there .. which you did, but then tried to echo that out .. try this:

 

...[stuff above here]
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM `responses` WHERE `condition` LIKE '".$_POST['msg']."%' ORDER BY RAND() LIMIT 1";
$result=mysql_query($query);
$print=mysql_fetch_assoc($result);

echo $print['a_field_name'];

 

'a_field_name' would be a field from your table, ie: first_name, id, etc.

[darkeye]

Works Great,

Thanks!

 

On another related topic...

My field names are: condition, response.

 

I have this code for inserting data into the table:

$query="INSERT INTO responses VALUES ('".$condition."','".$reply."')";
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
mysql_query($query);
mysql_close(); 

 

Now suppose the $condition I enter has the same value as another entry in my table, but I still want it to be randomly selected using the code you provided above. (I still only want the one value returned though, "LIMIT 1")

 

The code would have to do something like this:

 

Search the table for the same condition, and if there is already one, then add an integer to the end of $condition, and then search again. (repeated until it finds an open condition to use.)

 

How would I go about doing this?

 

[mrMarcus]

you want to ensure there are no duplicates before inserting the value into the db?  once the values are inserted into the db, there is no appending to them.  this is to be done before the insertion.

 

am i following?

[darkeye]

Exactly.

 

I want to check to make sure there are no duplicate values before insertion. If there is already a value in there that is a duplicate to the one I want to insert, Then I need to change some stuff around.

 

I would like it to add an integer to the one I am about to insert, and then check again, if it exists, then add 1 to the integer, check again, add 1 if necessary.

 

Once I am able to insert the value, (no longer a duplicate from the integer), it should do so. It also needs to be selected using the LIKE statement, and randomly selected from the ones that have similar conditions.

 

Sorry for being confusing.

[mrMarcus]

sounds as though there is something fundamentally wrong with your database schema.  appending a digit (ultimately random, since no user is going to guess a $condition plus a number) to a $condition is really just creating an overly redundant table.

 

what is $condition, and what is this whole process trying to accomplish?  i don't know that you're currently on the right track.

[darkeye]

Let me post my entire code so you will see what I am trying to do:

 

<?php
$haystack=$_REQUEST['msg'];
$needle='<reply>';
$username="admin";
$password="adminpassword";
$database="database";
$pos1=stripos($haystack, $needle);
if($pos1!==false){
$pos2=$pos1+7;
$condition=substr($haystack, 0, $pos1);
$reply=substr($haystack, $pos2);
$query="INSERT INTO responses VALUES ('".$condition."','".$reply."')";
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
mysql_query($query);
mysql_close(); 
echo "success if no error message.";
}
else{
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM `responses` WHERE `condition` LIKE '".$_POST['msg']."%' ORDER BY RAND() LIMIT 1";
$result=mysql_query($query);
$print=mysql_fetch_assoc($result);
echo $print['reply'];
mysql_close();
echo $result; 
}
?>

 

I am building an Instant Messenger chat bot, which can be scripted by the user, using the simple command: [something]<reply>[reply].

 

The bot will [something] to its condition column, and [reply] into the reply column.

 

Now whenever someone types [something] to it, ($_POST['msg'] being what they typed) the bot will echo [reply] to the user.

 

This is fairly simple, and is accomplished using the above code.

 

Now what if I want to make it so that the bot can reply with two different answers, but both are triggered by the same condition. Would I not just add a unique integer to the end of the condition ( test0, test1, etc.), so that it could be called up by the SQL: "LIKE '$condition%' example posted above?

 

This way it should select one random entry that meets the same condition.

 

Sorry for not being clear, and I hope this clears up what I want to do

[mrMarcus]

i still fail to see the significance of appending a unique value to the $condition.

 

instead, you could just LIMIT 2 instead of just 1, no?  would use the same $condition, just pull 2 results from the db.  would that not have the same effect?

[darkeye]

The thing is that I only want the bot to reply with one reply.

 

So for example, If i have the following in my table:

 

condition                reply

test0                    thisisatest

test1                    thisisanothertest

 

Then when someone says "test," in the chat, it will pick up both of the above replies, and randomly choose one of them to echo back.

 

 

[mrMarcus]

i'm afraid this'll be it for me today.  this little snippet will display 1 random result from a max of 2, based on the query.

 

<?php
$sql = mysql_query ("
SELECT `reply`
FROM `responses`
WHERE `condition` LIKE '".mysql_real_escape_string($_POST['msg'])."%'
ORDER BY RAND()
LIMIT 2
") or trigger_error (mysql_error());

if (mysql_num_rows ($sql) > 0)
{
$res = mysql_fetch_array ($sql);

echo $res['reply'];
}
else
{
//no results;
}
?>

[darkeye]

I know the syntax is wrong since I can't get it to work:

 

But perhaps something like this would do what I am suggesting?

<?php
$haystack=$_REQUEST['msg'];
$needle='<reply>';
$username="admin";
$password="adminpassword";
$database="database";
$pos1=stripos($haystack, $needle);
if($pos1!==false){
$pos2=$pos1+7;
$condition=substr($haystack, 0, $pos1);
$reply=substr($haystack, $pos2);
$query="INSERT INTO responses VALUES ('".$condition."','".$reply."')";
$int=0;
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$print='stuff';
while ($print[0]!=''){
$testquery="SELECT * FROM `responses` WHERE `condition`='".$condition.$int;
$test=mysql_query($testquery);
$print=mysql_fetch_assoc($result);
$int=$int+1;
}
$query="INSERT INTO responses VALUES ('".$condition.$int."','".$reply."')";
mysql_query($query);
mysql_close(); 
echo "success if no error message.";
echo $int;
}
else{
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM `responses` WHERE `condition` LIKE '".$_POST['msg']."%' ORDER BY RAND() LIMIT 1";
$result=mysql_query($query);
$print=mysql_fetch_assoc($result);
echo $print['reply'];
mysql_close();
}
?>

 

It works for returning values fine but inserting them is the problem. It goes into the loop once but then exits automatically for some reason, making $int always 1.

 

Any suggestions?

 

P.S. Sorry for my bad coding, I'm new to PHP

[darkeye]

-Bump-

[darkeye]

I figured it out!

 

Here is what I needed to do to test for a record:

 

$loop="yes";
while ($loop=="yes"){
$testquery="SELECT * FROM `responses` WHERE `condition`='".$condition.$int."'";
$test=mysql_query($testquery);
if(mysql_num_rows($test) > 0){
echo "I was here.";
$int=$int+1;
}
else{
$query="INSERT INTO responses VALUES ('".$condition.$int."','".$reply."')";
mysql_query($query);
mysql_close(); 
echo "success if no error message.";
$loop="no";
}
}

 

Thanks a lot for your help mrmarcus!