Problem with MySQL and PHP

Question

PHP:

<?php
if (isset($_POST['name'])){
 mysql_connect("localhost", "db", "test") or die(mysql_error());
 mysql_select_db("db") or die(mysql_error());
 $tmp = mysql_query("SELECT commercial FROM Channels WHERE name='.$_POST[name].'");
 echo $tmp[0];
}
else
{
?>
<form method="post" action="<?php echo $_SERVER['PHP_SELF'];?>">
<input name="name" type="text">
<input type="submit" name="submit" value="submit" >
</form>
<?php
}
?>

MYSQL:

CREATE TABLE IF NOT EXISTS `Channels` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `commercial` tinyint(1) NOT NULL DEFAULT '0',
  `usrid` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Daten für Tabelle `Channels`
--

INSERT INTO `Channels` (`id`, `name`, `commercial`, `usrid`) VALUES
(2, 'TEST', 0, 0);

If i write TEST in the input the the in $tmp[0] is empty

please help

Answer 1

You've got mixed apostrophes and quotes here:

 $tmp = mysql_query("SELECT commercial FROM Channels WHERE name='.$_POST[name].'");

Ought to be:

 $tmp = mysql_query('SELECT commercial FROM Channels WHERE name="'.$_POST['name'].'"');

Edit: And, as others have mentioned, it'd be prudent to turn off Magic Quotes, if they're enabled, and to add mysql_real_escape_string(), so:

 $tmp = mysql_query('SELECT commercial FROM Channels WHERE name="'.mysql_real_escape_string($_POST['name']).'"');

Also, to clarify, the reason why I switched from outer quotes to apostrophes is that variable parsing (as opposed to doing the work yourself) adds a bit of overhead.

Answer 2

Your SQL string is probably the problem here...

$tmp = mysql_query("SELECT commercial FROM Channels WHERE name='{$_POST['name']}'");

Try this instead. I'm using {} braces to encapsulate the string. The . operator preforms concatenation strings, but only when outside of a string.

Eg,

$temp = "string one 'and' ;;: " . "two";

Answer 3

The problem is with your query:

$tmp = mysql_query("SELECT commercial FROM Channels WHERE name='.$_POST[name].'");

When you have an array item that you want to include in a string, you need to enclose it in curly braces:

$tmp = mysql_query("SELECT commercial FROM Channels WHERE name='{$_POST['name']}'");

---

However DO NOT DO THIS. This query is highly insecure and open to SQL injection. Firstly ensure that magic_quotes_gpc is set to off in your PHP configuration (it is insecure and unreliable -- you can use var_dump(get_magic_quotes_gpc()); to confirm whether it is on or not). Then do the following:

$tmp = mysql_query("SELECT commercial FROM Channels WHERE name='" . mysql_real_escape_string($_POST[name]) . "'");

This will help ensure that your site is not open to an SQL injection attack.

I would also encourage you to look into more modern and secure ways of doing database queries, such as PDO or MySQLi.

Answer 4

1. You have a syntax error:
   $tmp = mysql_query("SELECT commercial FROM Channels WHERE name='.$_POST[name].'");

You likely want:

$tmp = mysql_query("SELECT commercial FROM Channels WHERE name='" . $_POST['name'] ."'");

Otherwise if your name was "Some Thing" you're asking mysql for ".Some Thing." (also don't use that query as I've written it, it's a security problem, see below)

1. $tmp isn't an array it should be a result. You'll need to call one of the mysql_fetch_* functions to get the result. You should also check for an error using mysql_error() to make sure the query succeeds. For example:

   if( !mysql_error() ) { $row = mysql_fetch_row($tmp); // do something with the row of data }

2. The query as written is a security problem. Someone can use that to take over your database and do anything they want. Use mysql_real_escape_string() to avoid that:

   $tmp = mysql_query("SELECT commercial FROM Channels WHERE name='" . mysql_real_escape_string($_POST['name']) ."'");