MySQL query using PHP

Question

I cant seem to get this code to work. It keeps giving me the error "sorry: query failed". There is connection to the database cause if i input a wrong password it will give the error "mysql connection failed". The table pet is already created and populated with data. Please I need help

<?php
$conn = mysql_connect ("localhost", "vistor", "visitor", "test")
    or die ("sorry: Mysql connection failed");
$query = "select * from pet";
$result = mysql_query ($query,$conn)
    or die ("sorry: query failed");
while($row = mysql_fetch_array($result)) {
    echo $row['code'], " ", $row['name'];
    echo "<br>";
}
mysql_close($conn);
?>

Donny gave me the below code and i added some css to make it appear as a table but i am suspecting i have the coding wrong as I am getting errors. thanks

<html>
<head>
<title> php test script - hope this works </title>
</head>
<body>
<h1>php & mysql connection</h1>
<hr>
<?php
$db_host = "localhost";
$db_username = "vistor";
$db_pass = "visitor";
$db_name = "test";
$db = new PDO('mysql:host='.$db_host.';dbname='.$db_name,$db_username,$db_pass);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
$query = $db->query('SELECT * FROM pet');
echo "<table border = '2'>"
<tr>
<th>id</th>
<th>name</th>
</tr>
while ($row = $query->fetch()) 
{
 echo "<tr>";
 echo "<td>" . $row['id'] ."</td>";
 echo "<td>" . $row['name'] . "</td>";
 echo "<td>" . $row['price'] . "</td>";
 echo "</tr>;
    }
echo "</table>";
?>
</body>
</html>

`echo mysql_errno($conn) . ": " . mysql_error($conn) . "\n";` then stop uing mysql_* — , Nov 19 '14 at 20:05
first, the `mysql` commands are deprecated. check out the manual. secondly, see how you should debug this. The manual will tell you how to add the actual error to that `or die` part, and check out why your `mysql_query` didn't work. — Nanne, Nov 19 '14 at 20:05
The `mysql_` functions [are deprecated](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). You should switch to `mysqli_` — Machavity, Nov 19 '14 at 20:06
@Strawberry yes there is `mixed mysql_query ( string $query [, resource $link_identifier = NULL ] )` — , Nov 19 '14 at 20:08
I'd say that had you not included that `Please I need help` you'd have less downvotes — Félix Adriyel Gagnon-Grenier, Nov 19 '14 at 20:14
@strawberryshould i remove the $conn, whats the proper code to use for mysql? — Joseph, Nov 19 '14 at 20:14
@Joseph ignore strawberry, add the error checking line i provided and see what the **actual** error is — , Nov 19 '14 at 20:17

score 3 · Answer 1 · edited Nov 19 '14 at 20:36

3

Can you provide the full code for the database connection and query?

Keeping in mind that mysql_* functions have been deprecated in PHP, if you still want to use them, you should be able to do something like this:

<?php
//create connection to MySQL
$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password');

//select database
mysql_select_db('mysql_dbname', $link);

//create and execute query
$sql    = 'SELECT foo FROM bar WHERE id = 42';
$result = mysql_query($sql, $link);
?>

edited Nov 19 '14 at 20:36

Jay Blanchard

34,243
16
77
119

answered Nov 19 '14 at 20:07

Ally Rippley

31
1

I believe the issue is not selecting a database, try that and see if it fixes your issue. – Ally Rippley Nov 19 '14 at 20:11
I tried the code but no luck, its not showing anything and its not giving me an error like my code – Joseph Nov 19 '14 at 20:16
– Joseph Nov 19 '14 at 20:18
Please do not dump code in comments @Joseph. They're impossible to read. Edit your OP and add the code there, with an explanation. – Jay Blanchard Nov 19 '14 at 20:37

Donny · Accepted Answer · 2014-11-19T20:42:44.093

2

You should learn how to write the code in a prepared PDO statement. Since MySQL will be deprecated and writing it in MySQL can get you into trouble with injections. This is how you would write this code in a prepared PDO statement. You can write it in MySQLI, but the only thing is you would not be able to use your code with any other databases besides MySQL. With PDO you can use your code with any database like SQL, Access and more.

 <?php
    $db_host = "localhost";
    $db_username = "visitor";
    $db_pass = "visitor";
    $db_name = "test";

    $db = new PDO('mysql:host='.$db_host.';dbname='.$db_name,$db_username,$db_pass);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);

    $query = $db->query('SELECT * FROM pet');

    while ($row = $query->fetch()) {
     echo $row['code'], " ", $row['name'];

    }
    ?>

edited Nov 19 '14 at 20:42

answered Nov 19 '14 at 20:36

Donny

738
7
23

Also here is a good tutorial to help you get started. http://prash.me/php-pdo-and-prepared-statements/ – Donny Nov 19 '14 at 20:40
1

PDO with placeholders and exceptions is a lot easier to work with and debug. Nice example! – tadman Nov 19 '14 at 20:49
@Donny, you saved the day. That's the code I being looking for all day. Thanks it worked!!! – Joseph Nov 20 '14 at 03:13
np anytime if you need more help I will keep an eye out for more questions. Also check out these videos https://www.youtube.com/watch?v=cbiLi0CBHNM these might help ya. – Donny Nov 20 '14 at 03:22

MySQL query using PHP

2 Answers2