Help with PHP and MYSQL select function

Question

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.

Nicola Cossu · Accepted Answer · 2011-04-29 13:20:05Z

1

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.

answered Apr 29, 2011 at 13:20

Nicola Cossu

56.7k15 gold badges96 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Emmerman · Accepted Answer · 2011-04-29 13:21:54Z

1

$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!

answered Apr 29, 2011 at 13:21

Emmerman

2,34315 silver badges9 bronze badges

Comments

Headshota · Accepted Answer · 2011-04-29 13:19:10Z

0

SELECT * FROM $TB WHERE browser

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

answered Apr 29, 2011 at 13:19

Headshota

21.4k13 gold badges63 silver badges82 bronze badges

Comments

jimy · Accepted Answer · 2011-04-29 13:21:04Z

0

You update query will be like this

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

answered Apr 29, 2011 at 13:21

jimy

4,9183 gold badges37 silver badges52 bronze badges

Comments

Alec Smart · Accepted Answer · 2011-04-29 13:21:55Z

0

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)."'");

answered Apr 29, 2011 at 13:21

Alec Smart

96.2k39 gold badges124 silver badges187 bronze badges

Comments

Dmitriy Koval · Accepted Answer · 2011-04-29 13:27:09Z

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.

Collectives™ on Stack Overflow

Help with PHP and MYSQL select function

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related