[SOLVED] PHP/MySQL connection to database

[michalchojno]

I seem to struggle with establishing a simple connection to MySQL database using PHP and fetching data from SQL and displaying them on the screen.

 

Here is a code I wrote to carefully check if everything works: (yet it doesn't)

<?php

echo 'Start.<p>';

$db = mysql_connect('localhost', 'root', 'password', 'db'); // replace 'password' and 'db' with the right ones.

echo 'Check1: Connection to db.<p>';

$q = "SELECT id, owner from table where id = 69"; //when I execute this Select throu PhpMyAdmin I get 2 records as a result.

echo 'Check2: q defined as Select statement.<p>';

$r = mysql_query ($db, $q);

echo 'Check3: r defined as a query.<p>';

if ($r) {

    echo '

    <table>

    <tr>

        <td align="left"><b>ref</b></td>

        <td align="left"><b>owner</b></td>

    </tr>

    ';

    echo 'Check4: if r ok, create table's header.<p>';

    while ($row = mysql_fetch_array($r, MYSQL_ASSOC)) {

        echo '

        <tr>

        <td align="left"'.$row['id'].'</td>

        <td align="left"'.$row['owner'].'</td>

        </tr>

        ';

    }

    echo '</table>';

    mysql_free_result ($r);

}

else {

    echo 'System error.';

    echo 'Error: '.mysql_error($db).' Query: '.$q.'.';

}

mysql_close ($db);

echo 'End.<p>';

?>

 

As a result of this query, this is what I get:

 

Start.

Check1: Connection to db.

Check2: q defined as Select statement.

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in C:...file.php on line 18

Check3: r defined as a query.

System error.Error: Query: SELECT id, owner from equipment where id = 69.

End.

 

I don't understand why it doesn't work. I execture this Select statement directly in the database and it's OK. Yet the script here fails to generate the right results. Any tips why? How can I fix this?

[jamesxg1]

Hiya,

 

Firstly this line should be,

 

$r = mysql_query ($q, $db);

 

and try this,

 

$q = "SELECT `id`, `owner` FROM `table` WHERE id = '69'" or die(mysql_error());

 

James.

 

[PFMaBiSmAd]

@jamesxg1, putting an or die() on the end of an assignment statement ($q = ....) does not tell you anything. It should be on the mysql_query() statement. And in fact the code is already using mysql_error() in its' if() {} else {} logic.

[michalchojno]

I changed the syntax of mysql_query to

$r = mysql_query ($q, $db);

 

It still refuses to generate the right results. This is what I get:

Start.

Check1: Connection to db.

Check2: q defined as Select statement.

Check3: r defined as a query.

System error.Error: No database was selected. Query: SELECT id, owner from equipment where id = 69.

End.[/query]

 

Yes, the die() statement is not crucial. I'd first like to make the connection work and generate some results.

[PFMaBiSmAd]

So, the error message is telling you what the problem is -

No database was selected

 

You must select a database before executing a query - mysql_select_db

[michalchojno]

Argh, still not it.... Thank you so much for all this help and patience. This helps a lot, although this is frustrating. I still have an error.

 

OK, here is my modified code:

<?php

echo 'Start.<p>';

$link = mysql_connect('localhost', 'root', 'password'); // replace 'password' with the right one.

echo 'Check1: Connection to db.<p>';

$db = mysql_select_db('db', $link); // replace 'db' with the right one.

echo 'Check1a: DB defined.<p>';

$q = "SELECT id, owner from table where id = 69"; //when I execute this Select throu PhpMyAdmin I get 2 records as a result.

echo 'Check2: q defined as Select statement.<p>';

$r = mysql_query ($q, $db);

echo 'Check3: r defined as a query.<p>';

if ($r) {

    echo '

    <table>

    <tr>

        <td align="left"><b>ref</b></td>

        <td align="left"><b>owner</b></td>

    </tr>

    ';

    echo 'Check4: if r ok, create table's header.<p>';

    while ($row = mysql_fetch_array($r, MYSQL_ASSOC)) {

        echo '

        <tr>

        <td align="left"'.$row['id'].'</td>

        <td align="left"'.$row['owner'].'</td>

        </tr>

        ';

    }

    echo '</table>';

    mysql_free_result ($r);

}

else {

    echo 'System error.';

    echo 'Error: '.mysql_error($link).' Query: '.$q.'.';

}

mysql_close ($link);

echo 'End.<p>';

?>

 

And the results:

Start.

Check1: Connection to db.

Check1a: DB defined.

Check2: q defined as Select statement.

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in C:...file.php on line 25

Check3: r defined as a query.

System error.Error: Query: SELECT id, owner from equipment where id = 69.

End.

 

Any ideas what might be wrong?

[jamesxg1]

@jamesxg1, putting an or die() on the end of an assignment statement ($q = ....) does not tell you anything. It should be on the mysql_query() statement. And in fact the code is already using mysql_error() in its' if() {} else {} logic.

 

oh christ! lol sorry my mind is in wonder land lol sorry guy's

 

James.

[michalchojno]

Wait wait... I think I know.

 

mysql_query ($q, $link) and NOT mysql_query ($q, $db). Right?

[jamesxg1]

Yea!, how didnt we see this shame on me lol

 

yes that may just work lol

[michalchojno]

Let me check...

[michalchojno]

Wheeeew...... IT WORKS!!!

 

I really owe you guys. Thank you so much for your patient help. This forum is awesome!

 

How do I mark this thread as SOLVED?

 

Final question: Do I always need to use both mysql_connect () and mysql_select_db () ?