CODEWITHSUNDEEP
phpmysql
Nik
Nik

Reputation: 11

Help with PHP and MYSQL select function

I am creating a browser statistics script in PHP.
When PHP finds a match in a table how do I retrieve the row and pull all three fields of the table into separate variables.

My table rows are (browser, version, amount)

so I want to select everything from the TB were

browser = $browser and version = $version.
But when this happens how do I add one to the amount field and resubmit the row. Without altering anything else. Below is the code I have.

$browser_name= "MSIE";
$version = "7";
$query = ("SELECT * FROM $TB 
  WERE browser = '$browser_name' AND version = '$version'");
$result = mysql_query($query);
$count = mysql_num_rows($result);
if($count == 1)
{
  $mysql = ("SELECT * FROM $TB 
    WERE browser = '$browser_name' AND version = '$version'");

  //code to add 1 to amount would be here

}
else
{
  //if browser is not detected insert fresh row.
  $mysql = "INSERT INTO $TB (browser, version, amount)
    VALUES ('$browser_name', '$version', 1)";
}

I have been looking around for answers for ages but they just tell me how to select information out of a DB as a whole.

Upvotes: 1

Views: 668

Answers (6)

Dmitriy Koval
Dmitriy Koval

Reputation: 156

First of all, you should connect to db using

$db = new mysqli('host','user','pass','dbname');

Then, your query isn't right. You have a sql syntax error.

SELECT * FROM table WHERE column = value

So, without prepare, your code should look like:

$tb          = 'table_name'; // to avoid sql injections use addslashes() to your input
$browserName = addslashes('browser_name'); // like this
$version     = '7';

$db     = new mysqli('host','user','pass','dbname');
$result = $db->query("SELECT * FROM $tb WERE browser = '$browserName' AND version = '$version'");

// Than, you can fetch it.
while ($result->fetch as $row) {
    echo $row['yourColumn']; // will display value of your row. 
}

$db->query("INSERT INTO $tb (browser, version) VALUES ('$browser_name', '$version')";

}");

And, that's all. You can count your records using mysql count() method. Your code will work. I advice you, at first go to php.net and check the doc. There are many examples.

Upvotes: 0

Alec Smart
Alec Smart

Reputation: 95880

There are hundreds of issues with your query like sanitization etc. I suggest you read a good mySQL book to improve the queries. But to answer your question:

//code to add 1 to ammount would be here
mysql_query("update $TB set amount = amount + 1 where browser = '".mysql_real_escape_string($browser_name)."' and version = '".mysql_real_escape_string($version)."'");

Upvotes: 0

Emmerman
Emmerman

Reputation: 2343

$browser_name= "MSIE";
$version = "7";
$query = "SELECT * FROM $TB WHERE browser = '$browser_name' AND version = '$version'";
$result = mysql_query($query);

if($data = mysql_fetch_assoc($result))
{
    print_r($data);
    //use UPDATE from other answers if you need one here.
}
else
{
    //if browser is not detected insert fresh row.
    mysql_query("INSERT INTO $TB (browser, version, amount)
     VALUES ('$browser_name', '$version', 1)");
}

Beware of SQL injections!

Upvotes: 1

jimy
jimy

Reputation: 4908

You update query will be like this

UPDATE $TB SET ammount = 1 WHERE browser = $browser and version = $version

Upvotes: 0

Nicola Cossu
Nicola Cossu

Reputation: 56357

update table set amount_field = amount_field + 1 where browser = '$browser_name' and version = '$version'

I don't understand why you make twice the same select.

Upvotes: 1

Headshota
Headshota

Reputation: 21449

SELECT * FROM $TB WHERE browser

you have a mistake it must be WHERE instead of WERE.

Upvotes: 0

Related Questions