Query syntax error help

[John Lester]

I'm attempting to copy the subscribethread table to a new table called unsub_subscribethread. Once the table is created I need to copy the data from subscribethread to unsub_subscribethread. I'm still a novice with sql but what I have looks right to me, but both queries give a syntax error

$db->query_write("CREATE TABLE IF NOT EXISTS " . TABLE_PREFIX . "unsub_subscribethread LIKE subscribethread");

$db->query_write("INSERT " . " unsub_sunscribethread SELECT * FROM subscribethread ");

[Simon Lloyd]

Your query should be:
$db->query_write("CREATE TABLE IF NOT EXISTS " . TABLE_PREFIX . "unsub_subscribethread LIKE" .TABLE_PREFIX."subscribethread");

[kh99]

If you're using a table prefix, the second query also needs TABLE_PREFIX in there (twice, once for each table name).

[John Lester]

Ah thanks guys I didn't know I needed TABLE_PREFIX again, assumed it was taken care of the first time

--------------- Added [DATE]1344207241[/DATE] at [TIME]1344207241[/TIME] ---------------

Quick question, does it matter if the . has a space before and after TABLE_PREFIX?

Reason I ask is Simon's query has them without spaces in the 2nd part of the query.

[Simon Lloyd]

There's a space before but not after, this is because you want things similar to this
LIKE mytableprefix_subscribethread
otherwise it would look like this and fail
LIKE mytableprefix_ subscribethread
or this
LIKEmytableprefix_subscribethread

[John Lester]

I still don't understand why it's different in the creating part. In the creating portion of the query it's dot space table_prefix space dot space quotation. Why isn't it dot table_prefix dot quotation?

The online tutorials (at least the ones I have checked) don't even use the same punctuations. In some they use the comma and in others they use the period. Some say you don't need the semi-colon, others say you do. Some use quotation marks around text while others use single quotation marks. How the hell do you know which one is right?

[Simon Lloyd]

i think you're getting confused because vbulletin has done it's trick of adding a space after x amount of characters, here it is in quotes

$db->query_write("CREATE TABLE IF NOT EXISTS " . TABLE_PREFIX ."unsub_subscribethread LIKE " .TABLE_PREFIX."subscribethread");

so after TABLE_PREFIX. the quotation should be butt up against it because you ar etrying to get mytableprefix_ unsub_subscribethread if there was a space it would look like mytableprefix_ unsub_subscribethread and would create a table that would be called SPACEunsub_subscribethread (the word space wouldn't be there just a blank space )

So, in a nutshell the one in the quote in this post is correct if you dont wnt to introduce any other anomalies.

[kh99]

Originally Posted by John Lester

I still don't understand why it's different in the creating part. In the creating portion of the query it's dot space table_prefix space dot space quotation. Why isn't it dot table_prefix dot quotation?

What you're doing is building a string representing the sql for the query, so there's a lot of different ways that could be done. In the examples above it's being done by using the "string concatenation" operator (the dot) to put together multiple strings. The spaces that appear outside of quotes are optional, so that, for instance:

"The table prefix is " . TABLE_PREFIX . " for sql queries"

and

"The table prefix is ".TABLE_PREFIX." for sql queries"

Result in the same string, because the extra spaces are outside of the string "literals" so they're optional.

The online tutorials (at least the ones I have checked) don't even use the same punctuations. In some they use the comma and in others they use the period. Some say you don't need the semi-colon, others say you do. Some use quotation marks around text while others use single quotation marks. How the hell do you know which one is right?

I don't know about the comma thing - in php, a dot is used to concatenate strings and a comma doesn't work. (Unless you're talking about the SQL and not the php, in that case we'd have to see examples to explain it).

As for the semicolon, that's used to end an sql query but it isn't needed when doing queries using the vb functions. One string has to equal exactly one query, so there's no need for an end character.

In php you can use either single or double quotes. There are some differences (you should look at the php docs if you're interested), but in a lot of cases either one will work. If you're talking about use of quotes in the sql query itself, they are optional in some cases and in some cases they need to be back ticks (which can be confused with single quotes). Most likely the only thing you have to worry about (as far as valid sql) is putting single quotes around literal strings.

Having said all that, a couple of the queries posted above *are* missing a space - there needs to be a space before inserting the table prefix but not after, as Simon explained, but the query above is missing a space after LIKE.

I hope this helps - I'm afraid it might just be more confusing.

[Simon Lloyd]

Originally Posted by kh99

....Having said all that, a couple of the queries posted above *are* missing a space - there needs to be a space before inserting the table prefix but not after, as Simon explained, but the query above is missing a space after LIKE.

I hope this helps - I'm afraid it might just be more confusing.

ooops, fixed

[John Lester]

Originally Posted by kh99

Having said all that, a couple of the queries posted above *are* missing a space - there needs to be a space before inserting the table prefix but not after, as Simon explained, but the query above is missing a space after LIKE.

I hope this helps - I'm afraid it might just be more confusing.

Ok so can I just not use the spaces like so;

$db->query_write("CREATE TABLE IF NOT EXISTS ".TABLE_PREFIX."unsub_subscribethread LIKE ".TABLE_PREFIX."subscribethread");

Or do I have to have a space before the first TABLE_PREFIX like so;

$db->query_write("CREATE TABLE IF NOT EXISTS " .TABLE_PREFIX."unsub_subscribethread LIKE ".TABLE_PREFIX."subscribethread");

It's the mixed use of spaces and non spaces that doesn't make sense (at least to me at this point )