My php script is not using given username/pass/host rather using root@localhost (password: NO)

Question

Got a problem! Though I found almost similar threads but none helped :(

I've written a php script to fetch the number of registered users from my MySQL database. The script is working great in my localhost; it is using the given username,pass and host name which are "root", "root", and "localhost" respectively, but the script is not using the given username/pass/host rather using root@localhost (password: NO) in Live server.

In the Live server I created a MySQL user, set an different password, and hostname there is of course not localhost. I updated the script with my newly created mysql users data. BUT, whenever I run the script, I see that the script is still using "root", "root", and "localhost"!!

take a look at the script:

    //database connection
    $conn = mysql_connect( "mysql.examplehost.com", "myusername", "mypass" );
$db = mysql_select_db ("regdb",$conn); //Oops, actually it was written this way in the script. I misstyped it previously. now edited as it is in the script.

    //Query to fetch data
$query = mysql_query("SELECT * FROM regd ");            
while ($row = mysql_fetch_array($query)): 
$total_regd = $row['total_regd'];
endwhile;   

echo $total_regd;

-- Some says to change the default username and pass in the config.ini.php file located in phpMyAdmin directory. Would this help?? I didn't try this because either my hosting provider didn't give me privilege to access that directory (because I am using free hosting for testing scripts) or I simply didn't find it :(

Please help....

Answer 1

Foreword: The MySQL extension is marked as deprecated, better use mysqli or PDO

---

Though you store the connection resource in $conn you're not using it in your call to mysql_query() and you're not checking the return value of mysql_connect(), i.e. if the connection fails for some reason mysql_query() "is free" to establish a new default connection.

<?php
//database connection
$conn = mysql_connect( "mysql.examplehost.com", "myusername", "mypass" );
if ( !$conn ) {
    die(mysql_error()); // or a more sophisticated error handling....
}

$db = mysql_select_db ("regdb", $conn);
if ( !$db ) {
    die(mysql_error($conn)); // or a more sophisticated error handling....
}

//Query to fetch data
$query = mysql_query("SELECT * FROM regd ", $conn);
if (!$query) {
    die(mysql_error($conn)); // or a more sophisticated error handling....
}

while ( false!=($row=mysql_fetch_array($query)) ): 
    $total_regd = $row['total_regd'];
endwhile;   

echo $total_regd;

---

edit: It looks like you're processing only one row.
Either move the echo line into the while-loop or (if you really only want one record) better say so in the sql statement and get rid of the loop, e.g.

// Query to fetch data
// make it "easier" for the MySQL server by limiting the result set to one record
$query = mysql_query("SELECT * FROM regd LIMIT 1", $conn);
if (!$query) {
    die(mysql_error($conn)); // or a more sophisticated error handling....
}

// fetch data and output
$row=mysql_fetch_array($query);
if ( !$row ) {
    echo 'no record found';
}
else {
    echo htmlspecialchars($row['total_regd']);
}