mySQL query

lovefaithswing

Hi,
I have some MySQL questions. First, can I use combination operators in a query? For example:

$query = "SELECT * FROM schedule " .
               "WHERE user_id = '$user', task_id = '$task_to_add', work_id = '$time_to_add'";
$result = mysql_query($query) 
		or die(mysql_error());



if (mysql_num_rows($result) != 0)
{

$query = "UPDATE schedule " .
     		"SET time += '$time_to_add'" . 
     		"WHERE user_id = '$user', task_id = '$task_to_add', work_id = '$time_to_add'";
$result = mysql_query($query) 
		or die(mysql_error());
}

I am checking if there is an entry that fits my criteria and if so, I am adding more time to the it. If I can't do this, how would I do this?

And secondly, In this same example, I had to re-do the search when updating. Is there any way to just use the previous query in the next one?
Thanks
Hailey

NogDog

I think you want AND between your conditions rather than commas in your WHERE clause.

Since it would appear you are only using the SELECT query to determine if there are any records to update, it would probably be more efficient to skip that and just go ahead and do the update query, since it will only update any records which match that same selection criteria. (If it finds no matches, it will still take about the same amount of time as running the select query -- in fact it may take less since no data needs to be returned -- while if there are any matches you will have reduced the number of database transactions from 2 to 1.)

lovefaithswing

Thanks! I'll try that. Can I still use the mysql_num_rows to check if nothing was updated? If nothing is updated, I will be creating an entry.

And about my first question, Is the += in the query doable?

Here is what I changed my code to: (It doesn't seem to be working).


				$query = "UPDATE schedule " .
     				"SET time += '$time_to_add'" . 
     				"WHERE user_id = '$user' AND task_id = '$task_to_add' AND work_id = '$time_to_add'";
				$result = mysql_query($query);
				echo mysql_num_rows($result);


				if (mysql_num_rows($result) == 0)
				{
					$query = "INSERT INTO schedule " .
     				"(user_id, task_id, work_id, time)" . 
     				"VALUES ('$user', '$task_to_add', '$date_to_add', '$time_to_add'";
				$result = mysql_query($query);

				}

NogDog

You can call [man]mysql_affected_rows()[/man] after your update query to find out how many rows were changed.

lovefaithswing

thanks, now is there an easy way to add a number to a table entry and save the result as that entry without doing 2 database queries? Above, I tried to use += but I got an error.
thanks!

NogDog

. . . SET col_name = col_name + 1 . . .

lovefaithswing

Oh. hum.. I keep getting an error. WHat is wrong with this: (I tried it 2 ways. neither works)

$query = "UPDATE schedule " .
         	"SET time = time + '$time_to_add'" . 
         	"WHERE user_id = '$user' AND task_id = '$task_to_add' AND work_id = '$time_to_add'";
$result = mysql_query($query) 
		or die(mysql_error());

$query = "UPDATE schedule " .
         	"SET time = 'time + $time_to_add'" . 			       "WHERE user_id = '$user' AND task_id = '$task_to_add' AND work_id = '$time_to_add'";
$result = mysql_query($query) 
		or die(mysql_error());

The diff is in the single quotes. Wasn't sure it I needed them or how they went. I took them off completely and it still didn't work.

NogDog

What type of field is time? If it is a date, time, datetime, or timestamp field, you'll probably need to use one of the Date and Time Functions to do the time arithmetic.

If it is a numeric field of some kind, then there should not be a need for any quotes.