Search record in column

[super_human]

Hey guys ,
I’m not sure how to go about this:,basically i want a pattern/algorithm which can do the following:

(search the database column(s) for an entry/record){
if record exists ,Update the entry in that column}

if it does not exist{
echo "some statement";
}

Consider the following example:
There are 3 columns: name,income_per_month, net_income
Each column has 2 entries: john and jake,500 and 100,1000 and 2000

I would like to:
1.Search for “rick” in the name column and produce an echo msg saying "no such name as “rick” is present .
2.Search for “john” in the name column and update the net_income column by adding 1000 to it.

Could someone give me the basic guidelines on how to go about it.I’m a bit new to php/mysql

Thanks in advance

[OpzMaster]

Try this code. I use MySQLi instead of standard MySQL query code but it should still work. Make sure you replace the placeholders inside mysqli with your mysql server information and the “table” in the queries with the name of your table:

[php]<?php

$db = new mysqli(HOSTNAME, USERNAME, PASSWORD, DATABASE);

$name = $_REQUEST[‘name’];
$net_inc = $_REQUEST[‘net_inc’];

$result = $db->query(“SELECT * FROM table WHERE name=’$name’”);

if(!$result)
{
echo “Unable to connect to MySQL database”;
}
elseif($result->num_rows == 0)
{
echo “No such name as ‘$name’”;
}
else
{
$result2 = $db->query(“UPDATE table SET
net_income=’$net_inc’
WHERE name=’$name’”);

 if($result2)
 {
      echo "The information has been updated for name '$name'";
 }
 else
 {
      echo "Unable to modify information for name '$name'";
 }

}

?>[/php]

This code goes on the assumption that you will only have one record for the name you provide but that will always never be the case in a professional environment. I would suggest that you create a primary key in your table and perform the queries based on that primary key.

Hope this code helps.

[super_human]

thanks OpzMaster your code helped a lot.I have a question though, is it possible to update multiple columns in 1 update statement.
For example: mysql_query((“UPDATE name SET (result = result+ $value3) netincome = netincome+$value5…and so on”);

[OpzMaster]

Hi again,

Yes it is possible. You would separate all of the modified rows with commas as in this example:

[php]$db->query(“UPDATE table SET
row1=’$val1’,
row2=’$val2’,
row3=’$val3’
WHERE this=’$that’”);[/php]

Hope this helps.

[super_human]

Thanks again,OpzMaster
Hope my questions dont seem too simple,cause i got a lot more of those

Can i use a variable in the WHERE ?

<?php $variable = $_POST['name']; . .// some code mysql_query("UPDATE table SET result = `result`+$value3, `row1` = `row1`+$value4, `row2` = `row2`+$value5 WHERE name=[u]'[b]$variable[/b]'[/u]"); ?>

[OpzMaster]

Hi again,

If you look at my examples, you’ll see that I use nothing but variables. Because you are putting double-quotes around the query, you can put the variables inside without escaping the quotes like you would with single-quotes. But you have to watch out for variables with keys like arrays and such as if you put an array inside of double-quotes, you have to state the array like:

[php]$db->query(“UPDATE table SET row=‘value’ WHERE name=’$_SESSION[name]’”);[/php]

Notice there isn’t any quotes aroung the key. When you place an array of any type inside of the double-quotes, you can’t have the quotes in the key. Other than that, yes you can have variables inside of query statements as my examples showed above.

[super_human]

Thanks again OpzMaster