cancel
Showing results for 
Search instead for 
Did you mean: 
Go to solution
Highlighted

Sql not returning rows on remote database

Hi, 

i have a database hosted on godaddy, i have bought my domain from google. with my API i am able to connect to my remote database but i my sql is not returning any rows. i tried importing my database to local server using xxamp and there with localhost api,its returning rows..its just not working with remote database...can anyone tell me what might be the problem...

 

this is my api and code

xyzcrafts.com/ArConnect.php 

$servername = "localhost:3306";
$username = "user";
$password = "pass";
$dbName = "mydatabase";



// Make Connection
$conn = new mysqli($servername , $username , $password , $dbName);

// Check Connection
if(!$conn){
die("Connection Failed. ". mysqli_connect_error());
}
else echo("Connection Success") . "<br>";

//mysqli_query("SET CHARACTER SET utf8");
$sql = "SELECT * FROM `mydatabase`. `arvideo` " ;
$result = mysqli_query( $sql) ;
echo var_dump($result)."<br>";

if (!$result) {
printf("Error message: %s\n", mysqli_error($conn)) ;
}

if(mysqli_num_rows($result) > 0){
echo ("row greater than 0") . "<br>";
// show data for each row
while ($row = mysqli_fetch_assoc($result)){
echo "ID:".$row['ID'] . "|VideoURL:" .$row['VideoURL'] . "|VideoName:".$row['VideoName']. ";";
}
}
else {
echo ("row = 0 " ) ;
// die(mysqli_error($conn));
//echo 'Whole query: ' . $sql;
}

 

 

 

14 REPLIES 14
Super User III
Super User III

Re: Sql not returning rows on remote database

@xyzcrafts 

 

I see it says connection successful - and row 0

 

A couple things

1) Have you selected the database?

2) Have you granted access to the database from the remote IP address

 



I am a GoDaddy End User - Just Like You
Check out my site! | I currently manage over 300 WordPress Websites
* Please note that I offer free advice on this forum. If you would like personalized help, please contact me. Otherwise, please ask your question in the proper forum so the answer can assist EVERYONE in the community and not just you. Thanks! *

Once your issue is resolved,
please be sure to come back and click accept for the solution

Get Better Support on the Community Boards!
Etiquette When Asking for Help from the Community

Re: Sql not returning rows on remote database

hi , thanks for the quick reply , i have updated the question with the query i am using.I am using select query and i have given permission to the user in cpanel,are there any more permissions i have to give.

I am a unity developer and learning php , so i am new to this.

I am able to connect my database ..its just not returning any rows ...but i have 2 rows in my table.

the same code works when i import this database to local server using xxamp .its returning my 2 rows.

Also please tell me if i am posting in right section , i am new here.

Super User III
Super User III

Re: Sql not returning rows on remote database

@xyzcrafts 

 

When I first replied this morning I was on my phone so I didn't see the code you had posted.

 

So...

I would added 

if (!mysqli_query($conn , $sql)) {
    printf("Error message: %s\n", mysqli_error($conn));
}

This should tell you what your error message is

 

I would also update your sql query to be

SELECT * FROM `DATABASENAME`.`arvideo` 

Where the DATABASENAME is the actual database - I am assuming arvideo is your table name

 

Also... for the $conn are you using localhost or an ip address for the server location??

 

If it is an IP of a different server - on that server you'll want to make sure you run

 

GRANT ALL ON `DATABASENAME`.* TO 'username'@IPADDRESS_OF_SERVER IDENTIFIED BY 'password';

and then

FLUSH PRIVILEGES;


I am a GoDaddy End User - Just Like You
Check out my site! | I currently manage over 300 WordPress Websites
* Please note that I offer free advice on this forum. If you would like personalized help, please contact me. Otherwise, please ask your question in the proper forum so the answer can assist EVERYONE in the community and not just you. Thanks! *

Once your issue is resolved,
please be sure to come back and click accept for the solution

Get Better Support on the Community Boards!
Etiquette When Asking for Help from the Community

Re: Sql not returning rows on remote database

hi i have updated now with my full code(except username and password). I have also included error messsage query but i am not getting any error from that.

 

Super User III
Super User III

Re: Sql not returning rows on remote database

@xyzcrafts 

 

try it as 

$result = mysqli_query($sql);

You've already opened the connection previously so you don't need the conn variable in there

 



I am a GoDaddy End User - Just Like You
Check out my site! | I currently manage over 300 WordPress Websites
* Please note that I offer free advice on this forum. If you would like personalized help, please contact me. Otherwise, please ask your question in the proper forum so the answer can assist EVERYONE in the community and not just you. Thanks! *

Once your issue is resolved,
please be sure to come back and click accept for the solution

Get Better Support on the Community Boards!
Etiquette When Asking for Help from the Community

Re: Sql not returning rows on remote database

hi, updated the code with what u said, also i have added echo var_dump($result) and its returning null if its any help

Super User III
Super User III

Re: Sql not returning rows on remote database

@xyzcrafts 

 

I'm assuming you've already checked but just to make sure if you go into MySQL via phpMyAdmin (via cPanel) the table / rows are there correct???

 



I am a GoDaddy End User - Just Like You
Check out my site! | I currently manage over 300 WordPress Websites
* Please note that I offer free advice on this forum. If you would like personalized help, please contact me. Otherwise, please ask your question in the proper forum so the answer can assist EVERYONE in the community and not just you. Thanks! *

Once your issue is resolved,
please be sure to come back and click accept for the solution

Get Better Support on the Community Boards!
Etiquette When Asking for Help from the Community

Re: Sql not returning rows on remote database

yes its there . one thing i havent done and understand is GRANT priviledges. I am still not sure if i have to do this as i am able to connect to my database,but i am new to php so please tell me if i have to .

Super User III
Super User III

Re: Sql not returning rows on remote database

@xyzcrafts 

 

In cPanel if you go to the MySQL Databases section - there is a section to add the user to the database - go ahead and do that and on the 2nd screen select all the check boxes and submit

 

This is the "visual" way to do the grant command - it is possible that the user doesn't have the correct permissions



I am a GoDaddy End User - Just Like You
Check out my site! | I currently manage over 300 WordPress Websites
* Please note that I offer free advice on this forum. If you would like personalized help, please contact me. Otherwise, please ask your question in the proper forum so the answer can assist EVERYONE in the community and not just you. Thanks! *

Once your issue is resolved,
please be sure to come back and click accept for the solution

Get Better Support on the Community Boards!
Etiquette When Asking for Help from the Community

Re: Sql not returning rows on remote database

i was just checking that and yes i have granted all permissions to my user

Super User III
Super User III

Re: Sql not returning rows on remote database

@xyzcrafts 

 

At this point I'm going to tag in @MrVapor and @Nate two of the other super users in the community to make sure I haven't missed something - this maybe something that you will need to provide access to your server to someone to help troubleshoot further. There isn't anything that is jumping out at me otherwise at this point.



I am a GoDaddy End User - Just Like You
Check out my site! | I currently manage over 300 WordPress Websites
* Please note that I offer free advice on this forum. If you would like personalized help, please contact me. Otherwise, please ask your question in the proper forum so the answer can assist EVERYONE in the community and not just you. Thanks! *

Once your issue is resolved,
please be sure to come back and click accept for the solution

Get Better Support on the Community Boards!
Etiquette When Asking for Help from the Community

Re: Sql not returning rows on remote database

ok , i am ready to give access to my server , there is not really any private  data in it as i am just learning right now.

Super User III
Super User III

Re: Sql not returning rows on remote database

@xyzcrafts 

 

At this point, I think you either need to contact phone support/live chat and speak to their hosting department or hire a server admin to help you out. This one is beyond my scope personally.



I am a GoDaddy End User - Just Like You
Check out my site! | I drink and I know things. Especially about websites and web hosting.
* Please note that I offer free advice on this forum. If you would like personalized help, please contact me. Otherwise, please ask your question in the proper forum so the answer can assist EVERYONE in the community and not just you. Thanks! *

Once your issue is resolved,
please be sure to come back and click accept for the solution

Get Better Support on the Community Boards!
Etiquette When Asking for Help from the Community


Super User III
Super User III
Solution

Re: Sql not returning rows on remote database

@xyzcrafts 

 

Just to close this out and for anyone following the thread - 

 

1) I reset the permissions for the database user just to be safe

2) changed $server to localhost vs the IP - no port number was needed - as for as how the cpanel servers are, you would have needed the URL from phpMyAdmin actually vs the IP address in this case

3) Add $conn to the mysqli_query  - Depending on if you use mysqli-> query vs mysqli_query - the syntax for the code is slightly different



I am a GoDaddy End User - Just Like You
Check out my site! | I currently manage over 300 WordPress Websites
* Please note that I offer free advice on this forum. If you would like personalized help, please contact me. Otherwise, please ask your question in the proper forum so the answer can assist EVERYONE in the community and not just you. Thanks! *

Once your issue is resolved,
please be sure to come back and click accept for the solution

Get Better Support on the Community Boards!
Etiquette When Asking for Help from the Community