Database backup

[SkyRanger]

Can't find error

 

$dbhost = 'localhost';
$dbuser = 'username';
$dbpass = 'password';
$tblName = 'name of database' //ie: clients

$db = new mysqli($dbhost, $dbuser, $dbpass);

if($db->connect_errno > 0){
   die('Unable to connect to database [' . $db->connect_error . ']');
}

$today = date('Y-m-d');
$backupFile = 'backup/esafeclients-$today.sql';
$sql = "SELECT * INTO OUTFILE '$backupFile' FROM $tblName";

if(!$result = $db->query($sql)){
   die('There was an error running the query [' . $db->error . ']');
}

 

I am getting this error

 

There was an error running the query [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1]

[Jessica]

echo the query along with it and you'll see it.

 

The code you've posted shouldn't produce that error.

 

Edit: Oh yes it will. You have two possible issues.

Read this: http://dev.mysql.com/doc/refman/5.0/en/select.html

Edited January 16, 2013 by Jessica

[SkyRanger]

beating head off keyboard, trashed script, restarting from scratch, still can't find error. Thanks for the link (again), printed it off for future use and a reminder.

[stijn0713]

i don't have any problems with your script. ofcourse you need the database selected, in case you didn't?

[Jessica]

Read the SELECT syntax. The order in which you put clauses is important. Hint: FROM is nowhere near the end of it. INTO OUTFILE is.

 

But I feel like you didn't even try what I said...

[SkyRanger]

Now I feel real stupid, I looked right at that, went though my query didn't even see it, kicking myself now, redoing the query and let see what happens. Will reply with results. My apologies Jessica

Edited January 16, 2013 by SkyRanger

[stijn0713]

jessica, i don't understand what you are saying either. SkyRanger, as i said, i just think you forgot to select your database name:

 

$db = new mysqli($dbhost, $dbuser, $dbpass , 'databasename');

[Jessica]

Well he understood it.

 

The query is malformed. The right way is

"SELECT * FROM $tblName INTO OUTFILE '$backupFile'"

 

There is a big difference.

 

 

It appears he does also need to select a DB according to this code. It's possible he left that part out when copying it, because I don't think MySQL would return the error it did if that were the case.

Edited January 16, 2013 by Jessica

[stijn0713]

well, on my computer that doesn't make a difference

[Jessica]

I could be wrong. If he'd done any debugging on his own like echoing the query it would help. Because THAT ERROR indicates a DIFFERENT issue. Which is why I said echo the query. But I'm talking to myself now.

[stijn0713]

talking to yourself ? poor girl . anyhow, you are right about the error indication. dunno where that comes from either.

[Jessica]

I do, but no one will listen to me.

[SkyRanger]

I do, but no one will listen to me.

 

Just a hint Jessica I am male....

 

Yeah forgetting the database part was my error on posting.

 

Now I have a new problem. Using same username and password for my other scripts but for some reason it is saying

There was an error running the query [Access denied for user 'username'@'localhost' (using password: YES)]

 

This is now I hope the final piece of code:

 

$db = new mysqli($dbhost, $dbuser, $dbpass, $tblname);

if($db->connect_errno > 0){
die('Unable to connect to database [' . $db->connect_error . ']');
}

$today = date('Y-m-d');
$backupFile = './backup/esafeclients-'.$today.'.sql';
$sql = "SELECT * FROM $tblname INTO OUTFILE '$backupFile'";

if(!$result = $db->query($sql)){
die('There was an error running the query [' . $db->error . ']');
}

 

I have tried to echo $sql with no result.

 

Should I be using the username and pass that is attached to this database or should I be using my hosting account uname and pass.

 

PS: If you think I should just give up and do it manually through phpmyadmin just say so I wouldn't mind right about now....lol

Edited January 16, 2013 by SkyRanger

[Jessica]

Your code doesn't say echo $sql anywhere. 

 

And when you pasted the error here did you change it to username or are you trying to connect with 'username'?

Edited January 16, 2013 by Jessica

[PFMaBiSmAd]

The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected rows to a file. The file is created on the server host, so you must have the FILE privilege to use this syntax.

 

If this a shared hosting, it's unlikely you have or will be given the needed privileges to run this query.

[Jessica]

 

 

If this a shared hosting, it's unlikely you have or will be given the needed privileges to run this query.

 

Thanks for the link, mostly because it has this at the top:

"The SELECT syntax description (see Section 13.2.8, “SELECT Syntax”) shows the INTO clause near the end of the statement. It is also possible to use INTO immediately following the select_expr list."

 

So I go back to my very first thought which is one of those variables is empty, producing the ''. Probably the one surrounded by them in the first place. 

[mikosiko]

if what you previously posted, as you said is your last code, an immediate question is $tblName is your DATABASE name or your TABLE NAME? ... because you are using it for both

Edited January 16, 2013 by mikosiko

[SkyRanger]

Sorry guys, had to run out for a bit. $tblName is actually the Database name.

 

Sorry Jessica, never added that to this post. Didn't think it was relevant for trying to fix the code I needed. no actually it is suppose to be esafe_clients@

 

I know the output comes out at

select * from esafe_clients into outfile esafeclients-2013-01-16..sql

 

 

Just got the user privilages for this database. could this be the problem

 

 

esafe_clients

 

Alter, Create, create routine, create temporary tables, create view, delete, drop, execute, index, insert, lock tables, references, select, show view ,trigger, update

Edited January 17, 2013 by SkyRanger

[Christian F.]

That would certainly be a problem, seeing as you don't have the FILE privilege, yes.

Set the proper permission, and try again. If you can't do that, then you'll have to use PHP to generate the file instead.