Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

PHP

Chris Andruszko
Chris Andruszko
18,392 Points
Posted by Chris Andruszko
Chris Andruszko
18,392 Points

Trying to get user info from a remote SQL database (probably a newbie question)

Forgive me if this question is better suited in the WordPress section, but it has less to do with WP and more to do with PHP and MySQL.

I need to echo user info in a WordPress plugin (such as user_login, user_email, etc.) from a remote SQL database. I use the following code to create the connection

<?php
$link = mysql_connect(*db path*, *username*, *password*); 
if (!$link) { 
    die('Could not connect: ' . mysql_error()); 
} 
echo 'Connected successfully.'; 
mysql_select_db(*db name*); 
?>

Yes, I realize that mysql_connect is depreciated. But I'm also trying to figure out why WP doesn't like my mysqli code. ANYWAY, this code works fine, the connection is established. Now how do I actually read files (specifically, user info) from this remote database?

Thanks for any help!

1 Answer

Jason Cullins
PLUS
Jason Cullins
Courses Plus Student 4,893 Points
Jason Cullins
Jason Cullins
Courses Plus Student 4,893 Points

Here is what you can do if you're only expecting 1 result.

<?php
$link = mysql_connect(*db path*, *username*, *password*); 
if (!$link) { 
    die('Could not connect: ' . mysql_error()); 
} 
echo 'Connected successfully.'; 
mysql_select_db(*db name*); 

$results = mysql_query("SELECT * FROM table WHERE user_id = '1'");
if ( mysql_num_rows($results) != 0 ) {  // this means we have some results
    $username = mysql_result($results, 0, "username");
}

mysql_close();
?>

What the above is saying is this:

If there are some rows from our query then lets get the result for the field "username" from the table for row 0, which would be the first row returned.

Now if you are wanting to do the same thing if you have more than 1 row returned you can do this.

<?php
$link = mysql_connect(*db path*, *username*, *password*); 
if (!$link) { 
    die('Could not connect: ' . mysql_error()); 
} 
echo 'Connected successfully.'; 
mysql_select_db(*db name*); 

$results = mysql_query("SELECT * FROM table WHERE user_id = '1'");
if ( mysql_num_rows($results) != 0 ) {  // this means we have some results
    for ( $i=0; $i < mysql_num_rows($results); $i++ ) {
        $username = mysql_result($results, $i, "username");
        // need logic here to work with each username.
    }    
}

mysql_close();
?>

What that would do is setup a for loop to return the username for each iteration. So the first iteration of 0 would be the first row, 1 would be the second row, and so on.

Hope this helps.

Also, don't forget to close your sql connection when you're done with it! :)

Chris Andruszko
Chris Andruszko
18,392 Points
Chris Andruszko
Chris Andruszko
18,392 Points

Wow, thank you for that extremely detailed response. You just made my work WAY easier :)