MYSQL :: Creating New Table Based On Old Table

How To Delete From 1 Table Some Rows Based On Match Results In 2nd Table?
How to delete from TABLE-1 all rows with indexes "i" that match to index j=2 from TABLE-2?

TABLE 1:
+---+------+
| i | name |
+---+------+
| 1 | item1 |
| 1 | item2 |
| 7 | item3 | <-- delete all rows with i=5,6,7
| 6 | item4 | <-- delete
| 5 | item5 | <-- delete
| 5 | item6 | <-- delete
| 7 | item7 | <-- delete
+---+------+

TABLE 2:
+---+---+------+
| j | i | name |
+---+---+------+
| 1 | 1 | item1 |
| 1 | 3 | item2 |
| 1 | 2 | item3 |
| 2 | 5 | item4 | <---- j=2 => i=5
| 2 | 6 | item5 | <---- j=2 => i=6
| 2 | 7 | item6 | <---- j=2 => i=7
| 3 | 8 | item7 |
+---+---+------+

View Replies !

Selecting Data Form One Table That Is Based On An Entry In Another Table
This is the code I am using:

***********
SELECT co_name,city,country,logo_small FROM $info WHERE $info.co_name=$categories.co_name AND WHERE $categories.everyday_wear='y' ORDER BY co_name ASC
***********

I am using a while loop in php to loop through all of the records but no records are being displayed. What am I missing? Do I need to use a JOIN statement?

View Replies !

Updating Rows In Table B Based On Related Field In Table A
Ver 4.1.8-standard for apple-darwin7.6.0 on powerpc (Official MySQL-standard binary)

I am trying to do some data migration based. I have several tables that contain our legacy pkey field and I want to update the tables with new ID's.
I need to do this several times and have tried it several ways to no avail.

Table A
---------
companyID int(10) pKey
legacyID int(10) old legacy pkey

Table B
---------
bAID int(10) pkey
companyID int(10)
legacyID int(10)

Table A has values for both companyID (unique key) and legacyID.
Table B has values for bAID (unique key) and legacyID but companyID is empty.

I need to update tableB.companyID with tableA.companyID based on tableb.cSerialID to tablea.cSerialID relationship.

I need a query that will update ALL rows.

View Replies !

Updating Table Based Upon Matching Field In Second Table
I have a database of books that was originally created as a flat file.
Each record has a number of fields, including the authors name. I'm
trying to convert the database to something a little more efficient.
I've created a new table (called Authors) of unique authors names and
assigned each one a unique ID. I've added a new field in the original
table (called Books) for the author's ID. Now, I need to update the
original table with the author ID from the Author's table.

Something like this:

UPDATE Books SET AuthorID = Authors.AuthorID WHERE AuthorName =
Authors.AuthorName

This obviously doesn't work. Any assistance on how to forumulate this
query (or, if I'm headed down the wrong path, the correct way to do this
operation) greatly appreciated.

View Replies !

Select Data From 1 Table Based On Criteria From Another Table
is it possible to select all the data in one table based on a criteria from another table?

for instance i want to select all the therapist from massage_therapist WHERE massage_schedule.finish > 0.
i don't want merged results. i just need to list all therapist based on the where criteria from a different table.

these two tables have the therapist_id in common.

View Replies !

How Do I Generate Results Based On Totals Of Another Table But For 1st Table?
This is what I want to do:

1- I have Two tables: polls_created and votes

2- Table polls_created is like:

poll_id
owner
poll_subject

3- Table votes has the votes issued for a given poll, like this:

vote_id
poll_id
vote
vote_date

So what I need to do is to look at these 2 Tables and generate results based on values of these 2 tables.

How do I then generate this result:

MySQL Code:
SELECT poll_id, owner, poll_subject, COUNT(vote_id) AS number_of_votes FROM polls_created, votes
"sorted by polls that have gotten most number of Votes"

Of course "sorted by polls that have gotten most number of Votes" is not real MySQL

View Replies !

Update Table Based On Email In Another Table
I'm having trouble updating the entries from a table. The situation is as follows:

Customer table contains:
1) customer_email_address
2) customer_newsletter (value 0 or 1)

Visitor table contains:
1) email

The visitor table contains email addresses from customers that have signed up through another system.

I would like to update the customer table and set customer_newsletter to 1 where customer_email_address matches email from the visitor table.

View Replies !

Selecting From A Table Based On Info In Another Table
Essentially this is what i want to do. I have two Tables,

Table A- has the field "id" which is the primary key
Table B- has the field "id" which is the primary key

Select * from tableA where Table A.id is not in tableB.id

How can i form a statement that will accomplish the above.

View Replies !

Table Based On Info In Another Table
Essentially this is what i want to do. I have two Tables,

Table A- has the field "id" which is the primary key
Table B- has the field "id" which is the primary key

Select * from tableA where Table A.id is not in tableB.id

How can i form a statement that will accomplish the above.

View Replies !

Updating A Table Based On The Value In Another Table In Ver 3.23.54-Max
I am trying to update fields in table SOLAR_SYSTEM based on the values in RESEARCH_LEVEL table

This is a SQL that I am trying to use

UPDATE SOLAR_SYSTEM JOIN RESEARCH_LEVEL ON SOLAR_SYSTEM.USER_ID=RESEARCH_LEVEL.USER_ID SET
SOLAR_SYSTEM.CASH_METAL=SOLAR_SYSTEM.CASH_METAL+1000,
SOLAR_SYSTEM.CASH_CRISTAL=SOLAR_SYSTEM.CASH_CRISTAL+1000,
SOLAR_SYSTEM.CASH_ENERGY=SOLAR_SYSTEM.CASH_ENERGY+1000
WHERE RESEARCH_LEVEL.TYPE='M' AND RESEARCH_LEVEL.LEVEL=1;

I got error

"ERROR 1064: You have an error in your SQL syntax near 'JOIN RESEARCH_LEVEL ON SO
LAR_SYSTEM.USER_ID=RESEARCH_LEVEL.USER_ID SET
SOLAR_SY' at line 1"

I don't know what is wrong. Can I do such an update in Ver 3.23.54-Max in the first place ? IF yes, then what's wrong with my statement?

View Replies !

UPDATE Based On Value In Another Table
How do I update a table to remove orphaned references to a second table? I've deleted rows in the second table, which has a unique auto_increment key. The first table now has references to keys that no longer exist. I need to update the first table, setting the value to NULL where the referenced key no longer exists. Something like:

UPDATE table1 SET table2ID = NULL WHERE table1.table2ID NOT FOUND IN
table2.ID;

The NOT FOUND IN isn't SQL, of course, but I'm not sure what should go there.

View Replies !

Update Based On Another Table
Is it possible to do an update in MYSQL based on another table? I have version 3.23 and when I try to run this statement:

UPDATE ApplicationTbl
INNER JOIN AcademicTbl
ON ApplicationTbl.CampusID = AcademicTbl.CampusID
AND ApplicationTbl.Application_Period = AcademicTbl.Application_Period
SET ApplicationTbl.App_Status = 'Qualified'
WHERE AcademicTbl.Sem_OnCampus >= '1'
AND AcademicTbl.GPA >= '2.4'
AND AcademicTbl.Judicial_Sanction IS NULL

It keeps saying its wrong. Even though I know its not

View Replies !

Selecting From One Table Based On Another?
I have a table for products

and another table for the type of product it is like this:

I need this is be in a seperate table because one product can be several different types like ring,band,wedding,three-stone,etc.

but how can I then select a product based on criteria from the types table

say I wanted to select all products that are type = "Band" and type != "Fancy" ?

View Replies !

Join Table Based On Specified Condition
Is it possible to join two tables based on the specified condition in MySQL. For example, i have two tables t1,t2 and t3. The table t1 and t2 has field called name and the table t3 has field name class. I want to select the names from t1 or t2 based on the condition of class(t3). If class=4 then select name from t1, if class=10 then select name from t2.tell the query in mysql.

View Replies !

Deleting From A Table Based On Record Id
I need to execute the following query:

delete from history where id=(select min(id) from history where user = username);

this query doesn't work and gives me an error "ERROR 1093 (HY000): You can't specify target table 'history' for update in FROM clause"

How do I rewrite this query? Ofcourse I can go for two queries, but I'd prefer to do it with a single query.

View Replies !

Order By Based On Results From Another Table
I have a query that , and i need to order the result based on wheter or not the ID of my main table appears on another table, somewhat like this:

SELECT A.id AS Id, A.nome AS Nome
FROM main_table A
JOIN joined_table JT ON JT.id = A.id_cat
ORDER BY ("first whoever have a row in" another_table AT)

another_table stores the image paths related to main_table , i need to show all query results from main_table even if they don't have a image (row in another_table), but i need to show whoever have a image (a row in another_table) first...

I've tryied to join with another_table and even to use sobqueryes in ORDER B... but so far i got no results. it's even worst because this query is a monter with multiple lines (the sample is simplyfied...)

View Replies !

List Available Items Based On Prerequisite Table
I'm trying to develop such a scheme:

TABLE Prerequisites
PrerequsiteID (FK to Upgrades table; states a prerequisite for UpgradeID in the other column)
UpgradeID (FK to Upgrades table as well)

TABLE BuildingUpgrades
BuildingID (FK to Buildings table)
UpgradeID (FK to Upgrades table; states what Upgrade Building already has)

So it's a Building -> Building_Has_Upgrades -> Upgrades -> Upgrades_Have_Prerequisites thing (Building has 1..n Upgrades, Upgrade has 1..n prerequisites). In order for Building B to have an Upgrade U, that Building B has to have all the Upgrades which are required to have Upgrade U.

What I want to do is to list all buildings with all upgrades available for them. I've been trying for almost whole day to solve this issue with different inner, left and right joins, subqueries and what not but I can't really do it in a clean way.

The only working thing is a query, which I am ashamed of because it seems like a real waste of CPU time to me:

SELECT BUPG.BuildingID, PRE.UpgradeID, COUNT(PRE.PrerequisiteID)
FROM BuildingUpgrades BUPG
JOIN Prerequisites PRE ON BUPG.UpgradeID = PRE.PrerequisiteID
GROUP BY BUPG.BuildingID, BUPG.UpgradeID
HAVING COUNT(PRE.PrerequisiteID) =
(
SELECT COUNT(PrerequisiteID)
FROM Prerequisites
WHERE UpgradeID = PRE.UpgradeID
GROUP BY UpgradeID
)

What it basically does is takes all the available Upgrades, counts prerequisites that have been met by the building and checks if it's equal to the number of all prerequisites for that Upgrade. And it works but I still think there is a much better way to do it.

View Replies !

Table Creation Based On User Login
I'm trying to build a website that will allow the addition of users (w/ username, password, etc.).

That seems simple enough.

But is it possible to set something up (in PHP, for example) that will create a new table in the database for each new user?

Ex. John Smith signs up, is verified, and a PHP script (or Java, or whatever) creates a table specifically for him.

I know I'll need a master table of usernames, passwords, etc. These new tables would store certain user-specific data, such as preferences and other data the user might want to restrict access to.

I know I could store all of this information in a master table, but I'm trying to think ahead. Worst case scenario, if there are millions of users (unlikely, but not impossible), I'd hate to have all that data in one table, but if I have to, I can.

View Replies !

Counting Levels On Adjacency Based Table
Does anyone know a single query that could successfully count the number of levels in a table based on the adjacency model?

By "number of levels", I mean the number of LEFT OUTER JOIN that needs to be used in a query such like this one :

SELECT
level0.cat_ID AS level0_ID, level0.cat_parent_ID AS level0_parent_ID, level0.cat_name AS level0_name,
level1.cat_ID AS level1_ID, level1.cat_parent_ID AS level1_parent_ID, level1.cat_name AS level1_name,
level2.cat_ID AS level2_ID, level2.cat_parent_ID AS level2_parent_ID, level2.cat_name AS level2_name,
level3.cat_ID AS level3_ID, level3.cat_parent_ID AS level3_parent_ID, level3.cat_name AS level3_name
FROM ecom_categories AS level0
LEFT OUTER JOIN ecom_categories AS level1 ON level1.cat_parent_ID = level0.cat_ID
LEFT OUTER JOIN ecom_categories AS level2 ON level2.cat_parent_ID = level1.cat_ID
LEFT OUTER JOIN ecom_categories AS level3 ON level3.cat_parent_ID = level2.cat_ID
WHERE level0.cat_parent_ID IS NULL
ORDER BY level0_name, level1_name, level2_name, level3_name

View Replies !

Update Based On Row Offset From Begining Of Table?
Is there a way to update a table row based on its offset from the begining of the table. I want to be able to update based on a rows position when its brought up when all rows in a table are selected. ie; select * from table.

View Replies !

Detecting Duplicates Based On One Field In A Table?
I can't seem to find an answer anywhere for this as most people want to delete duplicate records.

I have a table, lots of fields, one of which is "title". I want mysql to return all the rows where "title" is the same. For instance if "title" was "Lord of the Rings" on id = 1 and id = 2, then the query would return both of the records, but ignore the others. It doesn't matter what the other field values are.

I have found some
SELECT COUNT( * ) AS num_entries, title
FROM title_table
WHERE title >1
GROUP BY title

But this doesn't seem to work, although looks like it's on the right sort of lines?

I'm not a Mysql expert, so my experience is mostly limited to the usual select, insert, update and delete queries!

View Replies !

Select Based On Two Rows In Mapping Table
I have a many to many relationship between a resource and tag:

resource - 1:M - tagged_resource - M:1 - tag

So a resource can have many tags and a tag can belong to many resources. The data would look like this:

RESOURCE TABLE
id: 1
name: resource1

TAG TABLE
id: 1
tag: new
id: 2
tag: tested

TAGGED_RESOURCE
resourceID: 1
tagID: 1
resourceID: 1
tagID: 2

How can I select the single row from the RESOURCE TABLE which has the tags 'new' and 'tested' in the TAGGED_RESOURCE TABLE?

I have tried using 'IN' but it acts like an OR operator rather than AND.

View Replies !

Update Column Data Based On Another Table
I've got 2 tables: 'city' that list over 2000000 rows and 'profile' where each row are associated to a city.

What I want to do is to update cities popularities based on profile without having to scan the hole 2000000 rows of 'city'.

So is it possible to make those 2 query in one so I do not need to do a php loop:

SELECT city, COUNT(city) FROM profile GROUP BY city;

then

UPDATE city SET popularity = COUNT(city) WHERE city.city_id = profile.city

View Replies !

Creating A Query Based On Dates
I am trying to write a query (in PHP) which selects from a database all of the items which are in the future. My query is as follows

SELECT * FROM news WHERE ((news.date)>$today ORDER BY date

where news is my database, news.date is the the field which holds the date for the item and $today will be replaced my current date. At the moment it seems to display all values, which suggest its not functioning properly.

View Replies !

Multiple Table Search Based On User Input
I'm working on a database containing our research data. I know very little about PHP, MYSQL, or database structure (except keep like data together in different tables and have a key field in each).

I'm currently working with 3 tables (tbl_2006 and tbl_2007 structures are identical): ....

View Replies !

Determining Which Table To Query Based On Data Within Tables
I have 2 tables:

default_categories
column 1: category_id
column 2: category_name
column 3: category_parent

custom_categories
column 1: custom_cat_id
column 2: custom_cat_name
column 3: custom_cat_parent

The custom_categories table won't necessarily have anything in it but if it does, I need to choose the data from the custom_categories table over the data from the default_categories table.

So if the default category has 3 rows with IDs | names:
123 | Dogs
456 | Cats
789 | Fish

And the custom category has 1 row with IDs | names:
456 | Very Cute Cats

I want my query of these 2 tables to produce the following IDs | names:
123 | Dogs
456 | Very Cute Cats
789 | Fish

I've tried joins like the one below but they aren't working because if there is no custom_cat_id, it won't give me the result for the default category_id.

MySQL
SELECT *
FROM default_categories
LEFT JOIN custom_categories ON category_id = custom_cat_id
WHERE category_parent = ''
AND custom_cat_parent = ''
ORDER BY $order_by $sort

View Replies !

How Do I Replace Data In An Existing Table - Based On A Column With An Identifier
Each record in the table I want to update has a unique identifier: products_model. For a given model number, I want to replace its image, which is located in a field called products_image.

The file with the new data is a .txt file, a sample of which looks like this:

v_products_modelv_products_imageEOREOR
5361453614.jpgEOREOR
5361553615.jpgEOREOR
5372453724.jpgEOREOR

The beginning part of the table looks like this:
+----------------------------------+---------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------------+---------------+------+-----+---------------------+----------------+
| products_id | int(11) | NO | PRI | NULL auto_increment |
| products_type | int(11) | NO | | 1 | |
| products_quantity | float | NO | | 0 | |
| products_model | varchar(32) | YES | MUL | NULL | |
| products_image | varchar(64) | YES | | NULL | |
| products_price | decimal(15,4) | NO | | 0.0000 | |
| products_virtual | tinyint(1) | NO | | 0 | |

From what I've read over the past few days...I think I need to:
(a) delete the EOREOR column from the text file;
(b) use the LOAD DATA INFILE command somehow, telling it to ignore the first line of column headers in the text file.

View Replies !

Returning Rows In Left Table Based On Mutliple Criteria In Right
This is starting to get to me. I'm sure there's a simple way of handling what i'm trying to do, but someone might be able to help out quicker than spending another hour searching and testing for this. Here's the problem:
Simplified tables:
ARCADES
=======
ID,
name
GAMES
======
ID,
name
ARCADES_GAMES
=============
ID,
arcade_iD
Games_ID
Straightforward enough so far right? I'm trying to get all arcades that have ALL games in a passed in set of game_id's. So for instance I might want all arcades that have the games 11 and 14, but it must have both those.
I can do soemthing like...

SELECT a.name
FROM arcades a
WHERE EXISTS(SELECT 1 FROM arcades_games
WHERE arcade_id=a.id AND game_id IN (11,14))
But that'll return all rows that match ANY of (11,14) rather than ALL of 11,14.

It all comes down to the simple thing of getting rows in a table where all criteria from a list is met, but any advice on how i would do something like this?

View Replies !

Creating This Table
I have this schema I exported from a database running mysql version 4.01
I'm trying to create this table on a server running mysql 3.23
I keep getting a syntax error near line 11 (I added the line numbers for
clarity. I think it has a problem with the Table Field "ORDER#" I do
have it quoted as shown below so why do I keep getting syntax errors? I
also have problems with the Table Fields "BY" and "DESC" and they are
quoted the same way.

1 CREATE TABLE `tabPO` (
2 `PONUM` int(11) NOT NULL auto_increment,
3 `VENDOR` varchar(12) default NULL,
4 `STATUS` varchar(12) default NULL,
5 `TYPE` varchar(5) default NULL,
6 `PODATE` datetime default NULL,
7 `ORDERED` datetime default NULL,
8 `EXPECTED` datetime default NULL,
9 `SHIP` varchar(10) default NULL,
10 `CLOSEDATE` datetime default NULL,
11 `CHARGES` double default NULL,
12 `ORDER#` varchar(12) default NULL,
13 `NOTE` varchar(80) default NULL,
14 `SHFEE` double default NULL,
15 `PINVOICE` varchar(16) default NULL,
16 `SEPARATE` int(11) default NULL,
17 `LOCATION` varchar(5) default NULL,
18 KEY `PONUM` (`PONUM`)
19 ) TYPE=MyISAM; .

View Replies !

Creating A Table From Another
how to do a new table out of an old table, but with only uniques entries? The problem is that: I got a table with about 27k entries, but many of them are dublicate like that:

id|column1|column2
1|a|bcd
2|b|cdf
3|b|ddf
4|b|cdf
5|c|bcd
6|a|bcd

Now I want in that example e.g. entry 4 and entry 6 removed...

View Replies !

Creating Table
I am trying to Create this database, but when try it gives me an error:

MySQL said: Documentation
#1005 - Can't create table '.databaseattendance.frm' (errno: 121)....

View Replies !

Creating A New Table
I'm trying to create a table and, by using phpAdmin I am given this SQL

CREATE TABLE `tblDailyEvents` (

`DailyEvent` TINYTEXT( 255 ) NOT NULL ,
`EventType` TINYINT( 3 ) NOT NULL ,
`DateOfEvent` DATE NOT NULL ,
`ResourceLink` TINYTEXT( 255 ) NOT NULL ,
PRIMARY KEY ( `DailyEvent` )
) COMMENT = 'Daily Info for home page'

However, when I try to action it I get an error telling me to consult the manual with Error #1064.

I just don't understand the manual - it's simply too technical for me to understand.

Why create this code if it doesn't work? And what should the SQL be changed to?

View Replies !

Creating New Table
Hi, I am using Apache2 + PHP5 + MySQL 4.0.24_Debian-10ubuntu2-log, also MySQL Administrator 1.0.22 is installed. I am running this services on Kubuntu Linux. I am very new to mySQL so I need your help on adding new tables and all that. I am currently toying the MySQL Admin add new schemata and add table on it. I just put name as table name then {apply changes} a confirmation table creation pop-out with this syntax:
CREATE TABLE `test`.`Name` (
)
ENGINE = MYISAM;

When I press execute, this message appears:
Error executing SQL commands to create table.
MySQL Error Nr. 1064
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 ')
ENGINE = MYISAM' at line 2 .

View Replies !

Creating A New SQL Table
I've been having problems with creating a new table using a php page,
this is the code i use:

$query = "CREATE TABLE `pictures` (
`id` int( 11 ) NOT NULL ,
`gebruiker` VARCHAR( 255 ) NOT NULL ,
`foto` VARCHAR( 255 ) NOT NULL ,
`description` VARCHAR( 255 ) NOT NULL
);";
$result = mysql_query($query);

echo "Table <b>Pictures</b> Created";

But this doesn't seem to work. Because it isn't doing something, not executing the query, how can i let it execute it?

View Replies !

Creating A Table
I made a following code for creating a table....

View Replies !

Creating Table View
I had a table called 'FruitSaleList', that contains the following columns

sl.no, shop_id, fruitName, price, ....

I had also another table called 'FruitList' that contains the columns

shop_id, fruitName, salesManId, ....

is 'FruitList' table doesn't contain a column like fruitId.

Let us assume there are following fruits are available in all shops

Apple

Banana

Cherry

Dates

Elderberry

Figs

Grapes

Jackfruit

Orange

(it is a lengthy list, around 50)

In some case I need to list the details of fruits after 'Grapes' from 'FruitSaleList'. That means I have to list the 'Jackfruit' and 'Orange' from 'FruitSaleList'.

I'm struggling to list these using a MySQL query in php.

If I create a table view(FruitDetails) like below then I will proceed as specified below

|FruitId|FruitName|

|1 |Apple |

|2 |Banana |

|3 |Cherry |

|4 |Dates |

|5 |Elderberry|

|6 |Figs |

|7 |Grapes |

|8 |Jackfruit |

|9 |Orange |

View Replies !

Creating A Table With Name Condition
How would I create a table called "condition"? I'm not sure if this is a mysql command or what but I just want to CREATE TABLE condition, but it gives an error every time. Can it be done?

View Replies !

Creating A Dynamic Table
How to create mysql table with three fields all of which are variables, for eg. a keyword field, a number_of_hits field and a date field. a keyword can have different number_of_hits for different dates and there can be many keywords. The field entries has to be done dynamically for each day.

View Replies !

Error In Creating A Table
I am using the following to create a table, but I am getting an error.

CREATE TABLE cytology (cyt_id INT(11) NOT NULL AUTO_INCREMENT, smcl_id VARCHAR(20) NOT NULL UNIQUE, patient_id INT, doc_id INT, samp_obt DATE, date_rec DATE, source VARCHAR, lmp_date DATE, cyto_diag VARCHAR, samp_adeq VARCHAR, horm_assess VARCHAR, record_date DATETIME, PRIMARY KEY (cyt_id));

The error I am getting is:
ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version I can't see what is wrong with the above SQL. Can anyone help me, tell me what is wrong with it?

View Replies !

Creating A Temp Table ?
After querying my database for some perticular requirements i get 10 positive records form the 50 i ve query. I would like to then send back these 10 records to create a temp table with the resultes in mySQL is that possible ?

View Replies !

Not Null When Creating Table
I just started with MySql and I'm pretty excited. When I create a table, which attributes of the entities should be created with NOT NULL? I'm aware that the primary can't have NOT NULL, but what about TEX and all the other data types?

View Replies !

Creating Table Error
mysql_query("CREATE TABLE IF NOT EXISTS schooldb(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
name VARCHAR(30),
sname VARCHAR(30)") or die(mysql_error());

it gives me 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 '' at line 5

View Replies !

Creating A TIME Table?
Here is an example of a time I'm working on:

+-----------+---------------+
| record_id | last_modified |
+-----------+---------------+
| 3 | 14:46:32 |
| 10 | 16:48:26 |
| 11 | 17:21:03 |
| 12 | 11:56:23 |
+-----------+---------------+
etc...
It's simple. Shows a record and the time of day it was last modified at. What I'm wondering, is it more efficient to create a separate 'time' table, listing every second of the day (00:00:00 thru 23:59:59) and linking to this table in the last_modified column.

+-----------+-----------------------+
| record_id | last_modified_time_id |
+-----------+-----------------------+
| 3 | 35304 |
| 10 | 37030 |
| 11 | 41003 |
| 12 | 25030 |
+-----------+-----------------------+

(those numbers are just examples and are not the real numbers)

+---------+----------+
| time_id | time |
+---------+----------+
| 1 | 00:00:00 |
| 2 | 00:00:01 |
| 3 | 00:00:02 |
| 4 | 00:00:03 |
+---------+----------+
etc...

So basically you can see that one table simply holds all the time values for the day. The other table, instead of actually having a time value in its field, actually links to the time_id in the other table.

Is this efficient? Or a waste of time? Could I take it a step further and actually split up the time table into 3 other tables? Hours, Minutes, Seconds? Save even more space?

View Replies !

Creating Recursive Table
A recipe is made of products (potatoes, flour, etc.) as well as other recipes (dough, sauce, etc.)
how do I handle this in a recipe table?
I have the table recipe (ID_recipe, Name_recipe, recipe_recipe, price_recipe, category_recipe) and
a component table (ID_component, ID_recipe, name, quantity, etc)
I don't see how to include the fact a recipe can be made of another or several other recipes.
also how can I link to a recipe several categories?

View Replies !

Creating A Table To Use With A Drop-down
I know that I need two tables, but do not know how to create the second table or how to properly link them.

Some background - I have a site that sells leather motorcycle apparel. I need to be able to link the available sizes to the various styles.

Table 1 will be the main product table with the Category (Jackets, vests, etc), the SKU, Product Description and one or more graphics to show the product.

Table 2 (the one I am having problems with) will match the SKU to the available sizes. Sometimes the sizes are numeric and other times they are XS, S, M, L, etc.

View Replies !

Error Creating A Table
The MySQL is giving me problems at the start by not letting me create a database because of the following error: 1064 (42000) My MySQL connection id is 5 to server version: 4.1.12a-nt Code:

View Replies !

MQSQL V4.0 Creating Table
when I try to connect via the MySQL Adminstrator (windows app), I get an error:

"Host 192.168.1.101 is not allowed to connect to this MySQL Server"

notes:
1. mySQL administrator is running on the same server as the db
2. I can create a database using the command line "mysqladmin create uportal", but it doesn't appear in the utility with the stop lights?

View Replies !

Error In Creating Table
Im trying to create a table in mysql using the following command at mysql prompt, conncetd via SSH (telnet)

CREATE TABLE new_jobs
(
ID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(ID),
desc VARCHAR(60),
refno VARCHAR(15),
field VARCHAR(30),
qual VARCHAR(30),
exp VARCHAR(20),
locate VARCHAR(20),
posted VARCHAR(10),
type VARCHAR(10),
detail VARCHAR(1000),
keyword VARCHAR(500),
addinfo VARCHAR(1000)
);

It gives me an syntax error as follows:

ERROR 1064: You have an error in your SQL syntax near 'desc VARCHAR(60),refno VARCHAR(15),field VARCHAR(30),qual VARCHAR(30),exp VARCHA' at line 1.

View Replies !

Creating A Table In Mysql 5.0.18
I am trying to create a table by name schema in the mysql 5.0.18.I am not able to do the same in the mysql.

It is giving me the following error.

mysql> CREATE TABLE schema ( vseq INT UNSIGNED NOT NULL, ctime DATETIME NOT NULL, PRIMARY KEY (vseq));
ERROR 1064 (42000): 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 'schema ( vseq INT UNSIGNED NOT NULL, ctime DATETIME NOT NULL,' at line 1
mysql>

Can any one help me in resolving this issue.

Thanks in advance.Pls help me if any one's knows this.

View Replies !

Collation When Creating New Table.
OK as I use phpMyAdmin to create a new table, it asks me what collation I want to use for each field.

Below the table then, it asks me what collation I want to use for the table. Is this not a bit of duplication? If I set the collation for the table as 'latin1_general_cs', will that be the collation for all the fields if I don't specify them individually?

View Replies !