How to view or edit or create a table on a remote mysql server using php

Question

I am trying to access a mysql server on a remote machine using php. I tried viewing data in table, I am using below php script. Please let me know if I am missing something.

<?php
    $conn = mysql_connect("192.168.1.1","root","password");
    $db = mysql_select_db("charan", $conn);
?>

<?php 
   echo "<ul>";
   $sql = "select * from arista";
   $query = mysql_query($sql);

   while ($row = mysql_fetch_array($query)) {
         echo "<li>Username:$row[0]</li><li>DOB:</li><br/>";
   }
?>

missing a bracket here for one thing `$db = mysql_select_db("charan",$conn;` and a quote `echo "
Oh, sorry Fred that was a typo. I do have a bracket in my original script. — charan, Dec 17 '15 at 16:41
edited my comment above, there's something else. reload it and missing closing ul — Funk Forty Niner, Dec 17 '15 at 16:41
Add error reporting to the top of your file(s) right after your opening PHP tag for example ` — Funk Forty Niner, Dec 17 '15 at 16:42
root user in mysql have password? I think that you use default setting and root user not set password — paranoid, Dec 17 '15 at 16:43
You need to call SQL queries for creating table and editing table data. Where are they? — Somnath Muluk, Dec 17 '15 at 16:43
Somnath, in above example I am trying to view data from the table. — charan, Dec 17 '15 at 16:46
Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Dec 17 '15 at 16:47
@Fred-ii- I tried with above mentioned changes. I am getting below error. Fatal error: call to undefined function mysql_connect() in /var/www/sqlview.php on line 4. — charan, Dec 17 '15 at 16:55

score 3 · Accepted Answer · answered Dec 17 '15 at 16:55

3

For PHP >= 5.5 you will need to use MySQLi connector.

// this connect with the database
$con = new mysqli("192.168.1.1", "root", "password", "charan");
// here is the SQL query
$sql_query= "select * from arista";
// now execute the query
$result = $con->query($sql_query);

echo "<ul>";
while($row = $result->fetch_assoc()) {
    echo "<li>Username:" . $row['username'] . "</li><li>DOB:</li><br/>";
}
echo "</ul>";

It may works.

answered Dec 17 '15 at 16:55

Juanjo Salvador

1,073
1
12
33

Which version of PHP are you using? In `$row['username']` use the field name of the table where you have the username instead `username` – Juanjo Salvador Dec 17 '15 at 17:38
I tried above code and getting below error. Fatal error: Class 'mysqli' not found in /var/www/sqlview.php on line 4 – charan Dec 17 '15 at 17:41
It can be the constructor... `$con = new mysqli(HOST, USER, PASS, DATABASE);` – Juanjo Salvador Dec 17 '15 at 17:42
I am using php 5.3.10 – charan Dec 17 '15 at 17:42
Try updating to 5.5 (or 7.0) – Juanjo Salvador Dec 17 '15 at 17:43
Hi Salvador, Should it be $con or $conn ? – charan Dec 23 '15 at 17:26
It is a variable, you can name it con or conn – Juanjo Salvador Dec 23 '15 at 18:58

Nana Partykar · Answer 2 · 2015-12-21T12:49:39.230

[Note: Please stop using mysql* functions.]

Use mysqli* functions or Prepared statement. This way it is impossible for an attacker to inject malicious SQL.

Using procedural style:

<?php
$conn = mysqli_connect("92.168.1.1","root","password","charan");

echo "<ul>";

$query = mysqli_query($link, "select * from arista");

while ($row = mysqli_fetch_array($query, MYSQLI_NUM))
{
    echo "<li>Username:".$row[0]."</li><li>DOB:</li><br/>";
}

echo "</ul>";
?>

Using object oriented style:

<?
$conn = new mysqli("192.168.1.1", "root", "password", "charan");

echo "<ul>";

$result = $conn->query("select * from arista");

while($row = $result->fetch_assoc()) {
    echo "<li>Username:" . $row['username'] . "</li><li>DOB:</li><br/>";
}

echo "</ul>";
?>

Who Downvoted. Please give a reason. Don't Simply downvote and escape. — Nana Partykar, Dec 17 '15 at 17:18

score 1 · Answer 3 · answered Dec 23 '15 at 22:17

Final code that worked for me. After I did $apt-get install php5-mysql

    <?php
    error_reporting(E_ALL);
    ini_set('display_errors' , 1);

    $conn = mysqli_connect("192.168.1.1","root","password","charan");

    $sql_query = "select * from Boidata";

    $result = $conn->query($sql_query);

    echo "<ul>";
    while ($row = $result->fetch_assoc())
         { echo "<li>Username:" . $row['Username'] . "</li><li>DOB:" .$row['DOB'] ."</li><br/>";}
    echo "</ul>";
    ?>

How to view or edit or create a table on a remote mysql server using php

3 Answers3