/    Sign up×
Community /Pin to ProfileBookmark

[RESOLVED] Copying MySQL data

Copy linkTweet thisAlerts:
Sep 04.2014

All,

Another query has me stumped. I have tried a number of examples online, with no success.

I am trying to copy a few fields from one table to another. The examples I found online make it seem simple. Am I missing the obvious here?

I am trying this in it’s own PHP script, which is below. I thank you in advance for your help.

[CODE]error_reporting(E_ALL | E_NOTICE);ini_set(‘display_errors’, ‘1’);
$con = mysqli_connect(“localhost”, “timw79_rfid”, “xpress13”, “timw79_rxrfid”);
$query = “INSERT TagHistory (TagID, ReaderID, AssignedMachine, AssignedRig, Lat, Long)
SELECT TagID, ReaderID, AssignedMachine, AssignedRig, Lat, Long
FROM TagHistoryprep”;

if (!mysqli_query($con,$query)) {
die(‘Error: ‘ . mysqli_error($con));
}[/CODE]

I am getting the following error:

[CODE][COLOR=#000000][FONT=Times New Roman]Error: 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 ‘Long) SELECT TagID, ReaderID, AssignedMachine, AssignedRig, Lat, Long FROM Tag’ at line 1[/FONT][/COLOR][/CODE]

to post a comment
PHP

8 Comments(s) ↴

Copy linkTweet thisAlerts:
@NogDogSep 05.2014 — I'm guessing (too lazy to look it up) that "Long" is a MySQL keyword (for long integer), so you may have to quote it. The default mechanism for quoting table/column names in MySQL is the "back-tick":
<i>
</i><span><code>Long</code></span>

PS: It's never wrong to just quote all column names to be on the safe side, or else prepend the table name with the dot separator so that it's obvious to the parser what you mean.
Copy linkTweet thisAlerts:
@ginerjmSep 05.2014 — nogdog is probably right.

Just a tip thought for future coding. Write your code one statement to a line. Makes it easier for YOU to read and debug and maintain and makes it doubly easier for strangers to read (that's us when you need help).

Other things to do - always Check the result of an external operation. That is - when you call the database software (ie, mysqli) check the result. That means verify that your connection was made. Verify that your query ran before trying to fetch something or moving on after an insert or update. The same behavior applies to opening up files to be read or written to. Just like you test that a row has been fetched in a while loop, you need to test many other things. Makes for good code and easier debugging.

Here's how your code should look (IMHO):
[code=php]

// turn on error checking while in devl
error_reporting(E_ALL | E_NOTICE);
ini_set('display_errors', '1');
// open my db conn
if ($con = mysqli_connect("localhost", "timw79_rfid", "xpress13", "timw79_rxrfid"))
{
echo "Could not make database connection - Error msg is: ".mysqli_error($con);
exit();
}
// build & run my query
$query = "INSERT TagHistory (TagID, ReaderID, AssignedMachine, AssignedRig, Lat, Long)
SELECT TagID, ReaderID, AssignedMachine, AssignedRig, Lat, Long
FROM TagHistoryprep";
if (!mysqli_query($con,$query))
{
die('Error: ' . mysqli_error($con));
}

[/code]

You don't save anything by combining lines. It's all about writing good readable/re-usable code so that when you come back to it for any reason things are clear and understandable. Use indenting regularly.
Copy linkTweet thisAlerts:
@TW79authorSep 05.2014 — [code=php]if ($con = mysqli_connect("localhost", "timw79_rfid", "xpress13", "timw79_rxrfid"))
{
echo "Could not make database connection - Error msg is: ".mysqli_error($con);
exit();[/code]

This gave me a the "Could not make..." but did not return an error message. The above script looks like it should be a success message... I think.

Also, I have trouble now and then with posting code. The code was put in separate lines, not all strung together. Thanks for the tip, though.
Copy linkTweet thisAlerts:
@ginerjmSep 05.2014 — You are correct. Needs an ! in from of $con in the If.

Guess I should have that second cup of coffee!
Copy linkTweet thisAlerts:
@clevelandslimSep 12.2014 — I had read on various forums that "INTO" wasn't needed for this command. However, I added it as you recommended.

php: [Select]

<?php

error_reporting(E_ALL | E_NOTICE);

ini_set('display_errors', '1');

$con = mysqli_connect("localhost", "timw79_rfid", "xpress13", "timw79_rxrfid");

$query = "INSERT INTO TagHistory (TagID, ReaderID, AssignedMachine, AssignedRig, Lat, Long)

SELECT TagID, ReaderID, AssignedMachine, AssignedRig, Lat, Long

FROM TagHistoryprep";


if (!mysqli_query($con,$query)) {

die('Error: ' . mysqli_error($con));

}

?>

I get the same error.

php: [Select]

Error: 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 'Long) SELECT TagID, ReaderID, AssignedMachine, AssignedRig, Lat, Long FROM Tag' at line 1
Copy linkTweet thisAlerts:
@ginerjmSep 12.2014 — As mentioned by nogdog, you are probably using reserved words. Enclose them in backticks.
Copy linkTweet thisAlerts:
@TW79authorSep 12.2014 — That was the problem. Backticks saved the day! Thank you!
Copy linkTweet thisAlerts:
@ginerjmSep 12.2014 — Hth!
×

Success!

Help @TW79 spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 5.5,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,

tipper: @Samric24,
tipped: article
amount: 1000 SATS,
)...