MYSQL :: Prefix Table Name On Result Fields

Select All Fields Of Specific Row In Multirow Result
I'm making a query that returns multiple rows, each with multiple fields. I need to be able to select individual rows (including all of their fields) without using a while loop. so, my query:

$fullquery = "SELECT claimid, title, claim, dateSubmitted, totalVotes FROM claims ORDER BY overallrating DESC LIMIT 6";
$result = mysql_query($fullquery, $dbc);

I need to select row 1, use its data in a specific way, then select row 2, etc. I know how to do this in a while loop, and how to remove particular fields, but not how to access all fields for just 1 row.

Fields In One Table Overwrite Fields In Another Via JOIN
I'm writing a simple web game which uses mysql to store data. The prototype works ok so far, but now I'm refactoring it to support multiple players. This means coming up with a way to store the player's current world state.

I'm thinking of doing this by having a table that defines the initial world state, and then a table containing the 'state' of anything different.

Example:
The 'objects' table looks like this:
object_id, name, room
1, Hammer, 1
2, Mirror, 1
3, Spade, 2

So, at the start, room 1 contains a hammer and a mirror, and room 2 contains a spade.
Now, if player 12 picks up the hammer then drops it in room 2, I store this override in the 'state' table

player_id, object_id, room
12, 1, 2

So. I'm trying to see if there's any kind of join syntax that would return all the objects in a room for a given player's 'session'. Obviously it could be done with temporary tables, or just comparison outside of SQL, but I keep thinking this might be possible *somehow*

One Table With Many Fields Or Many Tables With Few Fields?
I need to build a database, but I'm torn between these 2 choices:

Is it better to have one table which has many fields
or
many tables which each has few fields?

Is it true that the latter is worse because it will require many join operations?
What is the limitation of the first option (one table with many fields)?

SELECT From A Table Depending On A Result From Another Table
This may sound a bit weird so i'll explain:

I have three tables:

tbl1foobar
------------------
ididid
type enum('foo', 'bar')infoinfo
typeid

Now I want to get the info from either foo or bar depending on tbl1.type
Is it possible to do this in one select statement?

Query Result As New Table?
How can I store a Query Result as a new Table?

Union Result Going To Other Table Alias
I need to check two tables for a value of 'pending'. Now, this value might exist or might not. So i figured a union would do the trick.

MYSQL
SELECT cats AS alias_one FROM table1 WHERE FIELD = 'pending'UNIONSELECT dogs AS alias_two FROM table2 WHERE FIELD = 'pending'
but if table2 is the only table with the result, mysql returns the contents of col_two but under the alias: alias_one.. how can i get around this?

Copy The Result Of A Query Into Another Table.
I am trying to make a select with a sum in it and group by account and copy the results in another table.

As the site has the 4.0.25 MySQL version i guess i cant make subconsults. I thought of storying the results in vectors, but up to now it didnt work ...

Add The Result Of One Query To The Contents Of One Table
I want to add the result of one query to the contents of one table:

For instance, the query is:

select count(*) from names;

and the other table is called spanish_names

Dropping Table Using Select Result
I have a shopping cart which creates temporary tables in the format of
zorder_<phpsessionid>;

These tables are used to hold a customers order while they shop and
after checking out, it is dropped. The values of the shopping cart are
put together in an HTML table and stored as their history.

What I want to do is to run a cron job to delete the orders which were
left undone once a day at about midnight. Here is my current query

SELECT table_name FROM orders WHERE RIGHT(started,8) < RIGHT(NOW(),8)
AND completed < 1 AND LEFT(started,10) = LEFT(NOW(),10);

I was hoping to do something like this:

DROP TABLE (SELECT table_name FROM orders WHERE RIGHT(started,8) <
RIGHT(NOW(),8) AND completed < 1 AND LEFT(started,10) = LEFT(NOW(),10));

But from my understanding, MySQL does not support nested queries.

Is there a query-based way to do this.

Filling Out Result Sets On Logging Table
I have a tabel which stores logging information (for visitor statistics and the like). Here's the create statement:

CREATE TABLE `stats_log` (
`id` int(11) NOT NULL auto_increment,
`ip` varchar(15) NOT NULL default '',
`host` varchar(255) NOT NULL default '',
`country` varchar(255) NOT NULL default '',
`language` varchar(255) NOT NULL default '',
`page` int(11) NOT NULL default Ɔ',
`browser` varchar(255) NOT NULL default '',
`version` varchar(255) NOT NULL default '',
`platform` varchar(255) NOT NULL default '',
`referrer_query` varchar(255) NOT NULL default '',
`referrer_domain` varchar(255) NOT NULL default '',
`referrer_path` varchar(255) NOT NULL default '',
`search` varchar(255) NOT NULL default '',
`visitor` varchar(40) NOT NULL default '',
`visit` int(11) NOT NULL default Ƈ',
`timestamp` int(11) NOT NULL default Ɔ',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Stores Statistics' AUTO_INCREMENT=1 ;
On the reporting page I run a number of queries, including this one to get the number of pages read within the specified period (user-selectable), grouped by and ordered by day of the month (unless the period is larger, in which case the results will be grouped and ordered by month instead of day).

SELECT
DATE_FORMAT(FROM_UNIXTIME(`timestamp`), '%d %b %Y') AS `timestamp`,
count(*) AS hits
FROM
stats_log
WHERE
`timestamp` > 1149112800 AND
`timestamp` < 1151704799
GROUP BY
YEAR(FROM_UNIXTIME(`timestamp`)),
MONTH(FROM_UNIXTIME(`timestamp`)),
DAY(FROM_UNIXTIME(`timestamp`))
ORDER BY
YEAR(FROM_UNIXTIME(`timestamp`)),
MONTH(FROM_UNIXTIME(`timestamp`)),
DAY(FROM_UNIXTIME(`timestamp`)) ASC
However, there are two things that are bothering me:

1 - there are a lot of date functions in there. Would it be better to expand the table to include day, month, year and hour columns and then to select the data based on those?

2 - is it possible, purely within the SQL, to also get the months, days or hours which aren't in the database? Pictures speak louder than words, so please consider this image:

As you can see, there is no data for between 3 o'clock and 11 o'clock this morning (as there were no hits). I can fill out those empty values with PHP, but I was wondering whether somehitng similar was possible with SQL.

Add A Prefix
I want to add a prefix to existing records incase of duplicate entries. I think the follow code is explanitory but I just have some syntax wrong.

UPDATE `products_to_warehouses` SET `warehouse_sku` = REPLACE('SYNC' & warehouse_sku) WHERE warehouse_id = 3

This is not working, what is the proper syntax?

Add Prefix
Want to add a prefix to a field of values.
Is this correct?

UPDATE t SET f = replace( f, 'prefix','f')
WHERE f like '%f%';

I want to add 'TD' to a value I have in a text field for many records.

Tables Prefix
What is a Tables Prefix in a MYSQL Database?
Can you show me an example of a Tables Prefix, using the extracted field names, from a database below?

user_iduser_group_iduser_nameuser_passworduser_emailuser_view_emailuser_icquser_aimuser_ymuser_msnmuser_homepageuser_cityuser_regionuser_countryuser_birthdayuser_occupationuser_interestsuser_languageuser_date_formatuser_time_formatuser_themeuser_signatureuser_postsuser_joineduser_avataruser_new_passworduser_activation_keyuser_topic_notificationuser_enable_pmuser_pm_notificationuser_invisibleuser_unread_pmuser_admin_emailsuser_forum_digestuser_visited_topics

How Can I Determine The Offset Of A Result In An Ordered Result Set?
How can I determine the offset of a result in an ordered result set?

I would like to pass the calculated offset into the limit half of and ordered select statement.

E.g. I have a table that records a id and datetime for captioned photographs. I'd like to show the five photos that were taken after the photo with id=23.

To do that I need to find the offset of photo with id=23 in

select id, datetime, caption from photos order by datetime;

Then I could get the result I want by doing....

select id, datetime, caption from photos order by datetime limit $offset, 5;

I've spent several hours scouring around and found some people with similar problems, but no solutions yet.

Duplicating Contents Of DB Within Same DB With Different Prefix
I revert to this forum to ask for help accomplishing a specific task. I'll try to explain as best I can.

Say I have a DB named... well... "database1", this db has the following tables:

pt_table1
pt_table2
pt_table3

What I want to do is copy each of the tables (exactly as they are, including primary keys, data, etc) but at the same time change the prefix to something else (ie: eng_) so that I would end up with:

pt_table1
pt_table2
pt_table3
en_table1 (exact copy of pt_table1, including data PK, FK, etc..)
en_table2 (exact copy of pt_table2, including data PK, FK, etc..)
en_table3 (exact copy of pt_table3, including data PK, FK, etc..)

Anyway of accomplishing this? Ideally without having to state each of the tables names. And also that this would work so that you could also only duplicate tables that have a certain prefix say: duplicate only tables that start with "pt_" and change prefix to "es_" for example.

Add A Prefix To A Field You Select?
Can you add a text prefix to a field you select from a select statement? Example:

Select field1, field2, field3 from table2.

I would like it to look like the following:

field1......field2........field3
Pre-<data> 34 square
Pre-<data> 22 circle

so i want to append "Pre-" to field1.

Deleting Tables With A Certain Prefix
How do I dump tables from my database which have a certain prefix?

I have more than one PHP/MySQL-applications in a shared database. Their tables are being separated by their corresponding prefixes "app1" and "app2".

Now that I want to get rid of one application I need to delete the tables from the databese.

How could I pass the query?
Do you have an example for my?
Does anyone know a PHP-script to do this?

Using 'N' Prefix For Unicode Strings
I have ported a .NET application which supports unicode from MS SQL Server
to mysql. The .net framework itself is unicode throughout and I am using
utf-8 innodb tables in mysql.

When working with mssql it was necessary to prefix unicode strings with N
e.g. N'a' when building the SQL to get them processed correctly. This
behaviour mostly seems to work in mysql unless there are single quote
characters in the string e.g.

insert into foo values ('a''b') -- works fine
insert into foo values (N'a''b') -- just inserts 'ab'

From reading the mysql manual I don't see any mention of the N prefix but
conversely the database engine doesn't seem to reject it.

Am I OK working with unicode strings in mysql without this N prefix? Any
idea why the prefix causes the quote processing to go wrong?

Adding Prefix/suffix In PK
I need to generate my primary key from my application bcos my PK has a specific format(e.g. Cl2006-09). Now to do this I need to know the last record/primary key of the last row(or only one record if exists only one)so dat I can generate my new PK by adding up to this last value.I heard about -mysql_insert_id() and last_insert_id() to get the current auto-incremented id but this will be meaningless if somebody else insert before i do.Now can anyone pls tell me-

1.How can I add any prefix/suffix(e.g.Year/any Char-'CL') to my Primary Key (Integer/BigInteger)

2.As I m adding prefix/suffix to my PK,how can MySql realize which is my last incremented PK (even I using mysql_insert_id() or last_insert_id).Bcos one id can have this value- CL0001-2008 and the next can have this value-CL0002-2007 (instead of CL0002-2008).

3.How can I make sure that nobody can insert during my insert using InnoDb engine.If table level lock(row level lock can't be applied here) is applied then locking granularity will be higher.Is there any other way?

Delete Tables With Prefix
How can i delete table have xxx_ prefix?

What Is The Command To Add The Prefix For The 20 Tables Of My Database?
I have created a database with 20tables, but now want to add the prefix "en_" to all the existing tables, so what is the exact command to do this job?

I have checked that to rename one to two tables can be done by:
"RENAME TABLE old_table TO backup_table, new_table TO old_table;"

but... how to do this for all the 20tables at once?

Backing Up And Restoring Tables, Different Prefix
How do I go about backing up tables under one prefix and restoring them into the same database but under a new prefix?

How To Enable Prefix Compression On MyISAM Indexes?
Anyone know? The manual suggests prefix compression (packing) is the default for all character type indexes, but "Packed" is always "NULL" in show indexes results...

mysql> show indexes from _1_log;
+--------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
| _1_log | 0 | PRIMARY | 1 | ID | A | 27456 | NULL | NULL | | BTREE | |
| _1_log | 1 | ip | 1 | ip | A | 3432 | NULL | NULL | YES | BTREE | |
| _1_log | 1 | countryCode | 1 | uniqueVisit | A | 2 | NULL | NULL | YES | BTREE | |
| _1_log | 1 | countryCode | 2 | countryCode | A | 188 | NULL | NULL | YES | BTREE | |
| _1_log | 1 | language | 1 | uniqueVisit | A | 2 | NULL | NULL | YES | BTREE | |
| _1_log | 1 | language | 2 | language | A | 85 | NULL | NULL | YES | BTREE | |
| _1_log | 1 | browserFamily | 1 | uniqueVisit | A | 2 | NULL | NULL | YES | BTREE | |
| _1_log | 1 | browserFamily | 2 | browserFamily | A | 42 | NULL | NULL | YES | BTREE | |
| _1_log | 1 | browser | 1 | uniqueVisit | A | 2 | NULL | NULL | YES | BTREE | |
| _1_log | 1 | browser | 2 | browserFamily | A | 42 | NULL | NULL | YES | BTREE | |
| _1_log | 1 | browser | 3 | browserVersion | A | 76 | NULL | NULL | YES | BTREE | |
| _1_log | 1 | os | 1 | uniqueVisit | A | 2 | NULL | NULL | YES | BTREE | |
| _1_log | 1 | os | 2 | os | A | 25 | NULL | NULL | YES | BTREE | |
| _1_log | 1 | resolution | 1 | uniqueVisit | A | 2 | NULL | NULL | YES | BTREE | |
| _1_log | 1 | resolution | 2 | resolution | A | 83 | NULL | NULL | YES | BTREE | |
| _1_log | 1 | depth | 1 | uniqueVisit | A | 2 | NULL | NULL | YES | BTREE | |
| _1_log | 1 | depth | 2 | depth | A | 10 | NULL | NULL | YES | BTREE | |
| _1_log | 1 | javascriptEnabled | 1 | uniqueVisit | A | 2 | NULL | NULL | YES | BTREE | |
| _1_log | 1 | javascriptEnabled | 2 | javascriptEnabled | A | 4 | NULL | NULL | YES | BTREE | |
| _1_log | 1 | searchEngine | 1 | uniqueVisit | A | 2 | NULL | NULL | YES | BTREE | |
| _1_log | 1 | searchEngine | 2 | searchEngine | A | 13 | NULL | NULL | YES | BTREE | |
| _1_log | 1 | uniqueVisit | 1 | uniqueVisit | A | 2 | NULL | NULL | YES | BTREE | |
| _1_log | 1 | visitorId | 1 | visitorId | A | 5491 | NULL | NULL | YES | BTREE | |
+--------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
23 rows in set (0.00 sec)
This is MySQL 5.0.27.

Prefix Blank Lines To A Simple Select Query
I would like to display a certain number of blank lines before the output of a select query, ie:

SELECT Var1, Var2 FROM SOME_SOMETABLE UNION ...?

Getting All Fields From A Table And ONLY ONE From Another One
maybe it's time to get some rest, today...

SELECT * , User_Avatar
FROM restaurantsrate, users
WHERE Card_ID =222443
AND Ratings_Deleted =0
AND Ratings_UserID = User_ID
LIMIT 0 , 30

I'm trying to get all fields from table restaurantsrate and only User_Avatar from table users, but I'm getting ALL fields from both tables? anyone can help?

How Many Fields Can A Table Have?
I'm presuming you can have as many fields as you'd like, but I'm just curious as to what the maximum recommended is. Is it ever better to split the fields over 2 tables instead of one? even though most the time if you're grabbing data from table1, you'll also be getting data from table2. The question popped into my head as I was making a "Orders" table for an online store. It has 39 fields so far and I'll probably need to add a few more (depending on what other information the client needs to know about their customer orders).

Table Fields
Is it ok to have a big table with 27 fields or should I try and split it down into smaller tables. It kind of makes sense all being in one table to me, but if that is bad design then I would break it down.

Sum 2 Fields From 2 Table
I have facing a problem to sum 2 fields from 2 table.

SELECT Table1.Stud_Number, Sum(table1.School_fee), Sum(table2.libary)
FROM Table1, Table2 WHERE Table1.Stud_Number = Table2.Stud_Number
GROUP BY Stud_Number

Db Table Fields
Is there an industry standard or best practice some of you more experienced developers could pass along concerning the number of characters I should define in my tables for a person's first name, last name, username, phone, email... and any other fields you think worth mentioning?

My Password is encrypted with sha1(), so that field is 40 characters long.

I'm looking to keep the tables as efficient as possible, so I'd like to follow some 'norms' if there are any.

Fields In Table
How do I fix this error? My tables have data.

More Than 50 Fields In A Table
I had made a table which contains more than 50 fields..
I am afraid this will hamper later ..
What i want to know ?
1>how many fields should be in a table for optimization ?
2>what should taken into consideration for future hazards in case there are more than
50 fields in table ?

SUM Of Two Foreign Table Fields
I've a project that has many timesheets (time) and many expenses (money)

I'm trying to work out the cost of the project base on a set amount per hour for timesheets plus expenses.

A project may not always have expenses.

A project has zero or many timesheets
A project has zero or many expenses

I need an SQL statement to return total time for a project and total expenses so i can do my calculations then in php. I'd prefer do this in one SQL statement.

What i have so far:

SELECT project.name, SUM(timesheet.time) AS time, SUM(expenses.amount) AS expense
FROM project, timesheet, expenses
WHERE project.id = timesheet.project_id
AND project.id = expenses.project_id
GROUP by project.id

==TABLES==

PROJECT
id PK
name

TIMESHEET
id PK
project_id FK
time

EXPENSES
id PK
project_id FK
amount
This will work fine for all projects that have both at least one expense and one timesheet. But if they are missing one then it returns no row for that project and therefore i can't display the cost.

There are other pieces of data but these are the only important ones. I can do it in two different SQL statements but i really think there has to be a way to do it in one.

Merging Two Fields In A Table!
I have used a join to bring two tables together, which works fine, but what I now have is 2 fields with dates in which I would like to order by, and at no point are these two dates overlapping, is there a way I can merge the two together, or order by both so as they overlap. My current SQL is:

SELECT * FROM tblblog
LEFT JOIN tblfilmreview
ON tblblog.blog_date = tblfilmreview.date_watched

UNION

SELECT * FROM tblblog
RIGHT JOIN tblfilmreview
ON tblblog.blog_date = tblfilmreview.date_watched

ORDER BY blog_date DESC, date_watched DESC
Thankyou for any advice you may be able to give
Dan Duke

How Many Fields Of A Table Are Recommended?
May be a stupid question, but just want to know, how many fields/columns are recommended ? I know, it depends how many someone needs, but incase if a person needs alot of fields, then for example, is it okay to make 200 fields of a table of mysql database? or its not good to add so many fields ?

Two Similar Fields One Table
I am trying to figure how to make a single select query in one table between
two fields to see if they are similiar. I need the second one to have a
wildcard. I can easily do this with no wildcard to see if they are the same
but I can't get anything to work when it should be LIKE.

Example:

Table: test
Field1 = "testing 123"
Field2="testing"

I need something that selects that row because the word "testing" is in
both.

Fields In Table Will Not Always Update,
I'm using C#, ASP.NET and MySQL, The language is no problem (not to much) but the rest well I wonder. I have a DB table that has 5 seperate fields. A main one that
is Integer and the rest are VarChar(50). each of the 4 others are identical in setup. I can change some records and others I can't.

ID int(10),
std varchr(50),
atd varchr(50),
dtd varchr(50),
ctd varchr(50)

I update say 'atd' with new data, Date, Time and a code of 15 letters/numbers.This one takes, I change to another record and try to update ctd and it shows like it takes but when I use the Command Line it shows no updates on ctd. this is random on this also.
If I change to another record it may all work or not. Any Ideas at all?