query works in mysql fails in php

Question

I'm new to php and have been trying to display my mysql database table onto a webpage. I've been working through the w3schools tutorial but can't get past this select data part (the reason I'm asking is not because of a tutorial but because I'm working on an assignment for class).

The connection works well but then the query always returns NULL. The query used to be more specific but now I'm just trying to get anything to display.

In mysql:

describe members

returns

+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| Name       | varchar(30) | YES  |     | NULL    |       |
| Type       | varchar(20) | YES  |     | NULL    |       |
| State      | char(2)     | YES  |     | NULL    |       |
| Price      | int(11)     | YES  |     | NULL    |       |
| Experience | int(11)     | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

-

select * from members;

returns (obviously not showing 20k rows):

20000 rows in set (0.05 sec)

In PHP:

<?php
    $conn = mysql_connect("127.0.0.1:3306", "root", "NotPostingOnSO", "coaches");

    if(!$conn) {
            die("Connection failed");
    }


    $result = mysql_query($conn, "SELECT Name FROM members");
    echo "vardump is: <br>";
    var_dump($result);

    mysql_close($conn);
?>

vardump returns NULL no matter what I do...

Note: I know about mysql_* and its deprecation but when I use mysqli_connect the connection fails...this website won't even be up in a week, I just need help with getting my database to appear as a table in php (end goal)

See vardump at http://45.55.175.147/findCoach.php which shows the error being Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE 1=1' at line 1

stack is Ubuntu 14.04, Apache2, PHP from instructions on digitalocean startup and same for mysql

UPDATE:

I changed mysql_connect to mysqli_connect as:

 $conn = mysqli_connect("127.0.0.1:3306", "root", "Hydro1$", "coaches");

            if(!$conn) {
                    echo "Error: Unable to connect to MySQL." . PHP_EOL;
                    echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
                    echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
                    exit;
            }

This returns error:
Error: Unable to connect to MySQL. Debugging errno: 2005 Debugging error: Unknown MySQL server host '127.0.0.1:3306' (11)

Update using mysql (not mysqli) Reverted to mysql_connect and fixed syntax so query is;

$result = mysql_query("SELECT Name FROM members");

vardump now returns bool(false); I feel like this is closer, but doesn't bool(false) imply the table members has no rows?

print the actual mysql error (example right from the docs) `` — pvg, Dec 08 '15 at 00:51
@pvg I tried your line but it return a syntax error for the snippet you gave me — Chris Herrera, Dec 08 '15 at 00:56
@Fred, could you advise how to do this properly? I tried the: $result = $conn->query("query here"); from w3schools, but it returns an error. Is there a procedural way to do it instead of doing it in this OO way? or is that just how mysql is? I'll rerun to tell you what the error is when doing conn->query() — Chris Herrera, Dec 08 '15 at 00:57
http://php.net/manual/en/function.mysqli-connect.php and http://php.net/manual/en/mysqli.query.php and http://stackoverflow.com/questions/17498216/can-i-mix-mysql-apis-in-php — Funk Forty Niner, Dec 08 '15 at 00:58
@ChrisHerrera mysql_query doesn't take connection as first param. — pvg, Dec 08 '15 at 01:01
have a look https://github.com/jewelhuq/Simple-php-crud-project/blob/master/dbconnect.php & learn how to do it using your own function — jewelhuq, Dec 08 '15 at 01:09
@ChrisHerrera you should still be printing the mysql_error on false. That will tell you what the error is. — pvg, Dec 08 '15 at 01:21
Using @jewelhuq 's code and the advice on mysqli was able to get it working. Thank you all. What can do I do to properly mark the answer? I can't pinpoint exactly what I did that made it work as I working off everybody's suggestions (went with mysqli). Should I post an answer, accept the one below...just do another update? thanks again — Chris Herrera, Dec 08 '15 at 01:25

score 1 · Accepted Answer · edited May 23 '17 at 10:27

Update

As your error points out, change 127.0.0.1:3306 to a simple localhost. You don't usually need to include a port as the mysql config has it defined.

As Fred said, you aren't using mysqli_ where you should be.

<?php
$link = mysqli_connect("127.0.0.1", "my_user", "my_password", "my_db");

if (!$link) {
    echo "Error: Unable to connect to MySQL." . PHP_EOL;
    echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
    echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
    exit;
}

$query = mysqli_query($link, "SELECT Name FROM members");
if(!$query){
    echo "Error: Unable to connect to MySQL." . PHP_EOL;
    echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
    echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
    exit;
}

print_r(mysqli_fetch_array($query,MYSQLI_ASSOC));

^{The above code can be cleaned up, but you should be debugging everything as it comes to ensure you haven't written error-filled code.}

Although, if you're in the process of it, you would be best to learn the OO Mysqli way or even PDO.

query works in mysql fails in php

1 Answers1