MYSQL :: Using A Field Alias In Another Field

Use An Alias Name For A Field In A Join Ation
I'm using a select like this:

SELECT * FROM `CPESER` INNER JOIN `CONCPE` ON `CPESER`.`id_concpe` = `CONCPE`.`id`
LEFT JOIN `CADSER` ON `CPESER`.`id_cadser` = `CADSER`.`id` LEFT JOIN `CADPRO`
ON `CADSER`.`id_cadpro_data`= `CADPRO`.`id` AND
`CADSER`.`id_cadpro_voice` = `CADPRO`.`id` LEFT JOIN `CONASS` ON
`CONCPE`.`contract` = `CONASS`.`contract` INNER JOIN `CADCLI` ON
`CONASS`.`id_cadcli` = `CADCLI`.`id` ORDER BY `CADCLI`.`name`

but there is a problem: some colums has the same name... it's possilbe to use aliases to the fields?

SQL, Field Name Alias For Write Access
I get an error on the adorec.update line - Unknown column 'MyName' in 'field list'. The code will work for read access but won't let me write to it. How do I update a field with an alias?

I know i can lose the "AS" part but that would defeat the purpose of what I'm accually trying to do.

Retrieve Field Value When Submitting To Table Auto Increment Primary Field Value
In my form, I write to four fields in the table, (fields 2,3 4 and 5), which creates a new record. the first field of this new record is an auto-incremental field, giving a unique id.

How can I write to the table and at the same time retrieve that unique id accurately, even if more than one person is performing write tasks simultaneaously?

My first thought is that if I grab the previous records id and add 1, then this may not match my newly inputted record, given that it may take a few minutes to add the record and someone else could have been there before me with a different record. (I hope that makes sense).

Then I thought that on opening the form, it could write to a new field inputting my loginID. Then I can retrieve the unique auto-increment number before then filling out my form and over-writing the loginID that I had previously entered. Sounds too convoluted to be the best way.

How To Convert A Varchar Field Into Proper Mysql Date Field?
I have a database in which the date is stored in varchar field in a following format: d-m-Y (06-08-2007), now the problem is that I want to change that field into mySQL date field as well as convert my older dates into MySQL date format i-e Y-m-d (2007-08-06)..

There are about 300 old entries..is there a way I can do that automatically without manually re-entering the dates again?

Extracting Field Data And Putting Each Field Into A Shell Variable?
In a MySQL query on the database (one table with 15 variable length fields,
I want to put each field into a Bash variable so that I can handle each
field as an entity.

The query I have is something like this:
mysql -e "use $database; select field1, field2, field3..., from Table1 where
fieldN like '%something%';"

can I / How do I do something like:
mysql -e "use $database; select field1,..., var1=field1, var2=field2,....;"

do
stuff with var1, var2...,
done

One Field With Multiple Attached Rows, Only Show The Field Once?
I have a table called "Customers", with a field called "Customer name" and a "Customer ID". I have another table called "Projects", with a field called "Project name" and a field called "Customer ID" which references to "Customers"

What i am trying to do is: Select a customer. Select the projects that are related to the customer by comparing the Customer ID field. Code:

Create A Field By Deleting A Part Of The String From Another Field!
I have a table for "Addresses", which has a column titled "Address" and the records in this column are a combination of house#, street name and street suffix, which typically looks like "100 Main Street".

I am looking for a query result wherein a new column would be created which would have only the street name and the suffix and would eliminate the house #s, hence the result would look like "Main Street".

Copy Field Over, Then Update The Original Field
I want to change a price of a product, but back up the old price. I was trying to do this through two queries, an INSERT and an UPDATE, but it's not working.

INSERT the row that will store the backup:

INSERT
INTO
s01_MUS_SalesXProducts
(
s01_MUS_SalesXProducts.sale_id
, s01_MUS_SalesXProducts.prod_id
) VALUES (
".(int)$sale_id."
, ".(int)$prod_id."
)
Then I would copy the prices over and update the original with one UPDATE query:

UPDATE
s01_Products
LEFT
JOIN
s01_MUS_SalesXProducts
ON
s01_Products.id = s01_MUS_SalesXProducts.prod_id
SET
s01_MUS_SalesXProducts.old_price = s01_Products.price
, s01_Products.price = ".(float)$sale_price."
WHERE
s01_Products.id = ".(int)$prod_id."
However it is copying the new price to the backup field. Is there a way to do this in two queries? I know if I select the product price before inserting I can do it in 3. Truely I would like to do this in 1, but I don't believe this is possible due to the nature of the queries.

Creating Field In Table To Sum Field Values
i was wondering if there was a way in mysql to have one record that will sum up all each field value(one record with each field having sum totals)? i have a table with over 60 statistical fields that i want to get the sum for.

Updating Field With Contents Of Temporary Field
I have a table, that when new information is entered for it the new info is held in a temporary table that has exactly the same fields (the only difference is the auto increment pid in the original table is not auto increment in the temporary one - it holds the pid from the original).

I need to be able to update the original with the contents of the temporary field, all fields are updated.

Is there an easy way to do this, or will I need to select from the temporary and then run the update setting all the fields explicitly.

I have tried something like [code]
Update villas SELECT * FROM updatevilla WHERE pid='$villapid'
[/code ] in php but it doesn't work. I think I see what some of the problem is but am not sure how to fully write the query.

Orderby A Text Field As It Were An Integer Field
Is it possible to construct a query so that the orderby 'field' returns the rows as though the 'field' were an integer field, even though it is a text field? I do not want e.g. 10 to be before 2.

The field in question is called 'lagerantal' and is of type 'text'

Import NULL Or Empty Field Field Value
If you are importing data from an external data source and you have the choice of populating empty fields with NULL, what are the pros and cons of doing so?

Copy Partial Field Value To New Field
I have a table with state and county in one field. The first 2 characters in all the records indicates the state. Is there a way to copy just these first two characters into a new field in mysql?

Given Field Name, Find Table That Has Tha Field Name?
Is there a quick way to identify tables that have a specific column in them? For example, if I have a column name 'RuleName', how can I need to find all the tables in a database that have a column with that name?

Date Field, Time Field
I have a MySQL date field (e.g 13/12/2006) and a MySQL time field (e.g.13:00) in the same row.

I'd like to join these two fields to make a date/time field (e.g. 13:00 13/12/2006) and to be able to sort the query results on the resulting field.

Can I Check The Value Of An Alias(i Think Its Called An Alias) In A Query.
I was wondering can I check the value of an alias(i think its called an alias) in a query.

At the moment I have this but its not working:

MySQL Code:

SELECT * , ( DAYOFYEAR( ToDate ) - DAYOFYEAR( FromDate ) ) AS NumDays
FROM `tbl_courses`
WHERE NumDays < 3 AND NumDays > 2 AND FromDate >= &#55614;&#57159;-10-17'
ORDER BY FromDate

I Want Field Data Without Field Name, How?
I am retrieving data from mysql under linux. something like mysql -e
"select id from table1", I get output

id
1
2

but I dont want the field name "id" but only the data. How can I do it?

Field Contains Form Field
I have a form putting variable search_var

Select * from training.itdtms_content where content_Body like #search_var#

the # on each side are for the Coldfusion variable.
I want to be able to find records that have a search term inside them, can anyone help?

Part Of A Field Into Another Field
I have a table with an email field that contains values of the form
uid@domain. I need to populate a new field called uid with just the uid
part of the email address. Is there any way of using select to just
retrieve the part before the @ and insert it into the uid field?

Help With Moving Data From One Table Field To Another Table Field
I am a relative newbie at this so would appreciate help - already searched and found and tried several suggestions for similar issues - but nothing quite worked!

I need to move a membership roster to another table in the same db - currently the data is at jos_users1 and it needs to be moved to jos_users. The jos_users1 table has only one field, named email.

The jos_user table has several fields including the email field, and already has data in it - so I don't want to overwrite the table - just upload the additional email addresses into the table.

Update One Table Field With Another Table Field
i am having a problem in my update function. I have two tables:

buyselldata table and autozack_signal table

buyselldata bsignal coloum has to be updated with Close_Price in autozack_signal table if macd_buy in autozack_signal is 1

common field in both tables are Script_Name

"Update buyselldata b
set b.buy = a.Close_Price
where exists(select Close_Price from autozack_signal a
where a.script_Name = b.script_Name
and a.macd_buy=1)";

i am getting -1 for mysql_error()

Updating Table Field From Another Table Field
I have a field called 'tel1' in table 'live_properties'
and I want to update a 'tel1' field in 'live_properties'

with

'phone_num' field from 'usersTBL'

MYSQL code is here but giving error any suggestions

UPDATE live_properties SET tel1 (SELECT phone_no FROM usersTBL WHERE username = live_properties.username)

Date Field, Time Field, Sort By Date And Time
I have a MySQL date field (e.g 13/12/2006) and a MySQL time field (e.g.13:00) in the same row.

I'd like to join these two fields to make a date/time field (e.g. 13:00 13/12/2006) on the fly and to be able to sort the query results on the resulting field.

Field Value = Foo AND != Bar
part of my query is "WHERE snafu = foo AND snafu != bar"

Can that be simplified? Like "WHERE snafu = foo AND != bar"?

Last Seen Field
On my 'users' field, i have the required fields for a user member registration.

CREATE TABLE `users` (
`Id` int(6) unsigned NOT NULL auto_increment,
`username` varchar(30) default NULL,
`pass` varchar(30) default NULL,
`fullname` varchar(100) default NULL,
`email` varchar(100) default NULL,
`created` datetime default NULL,
`acces` int(11) default '2',
`lastseen` datetime default NULL,
PRIMARY KEY (`Id`)
)

The registration datetime to be posted/called on various pages, admin stats, i am calling with: NOW()

Question: How can i solve the 'lastseen' field. What function to use? Or how to call it, i wan't to see on the admin users statistics pages, when a user member has accesed the website.

Can I Use ALL As A Field Name?
I have a table that has the 3 letter currency codes of the world currencies
as the field names. The Albanian Lek currency is know as ALL. When I goto
save to this field I get an error presumably because it is a word used in
MySQL. Is there anyway to store/read to/from this field keeping ALL as the
field name - without generating an error?

"INSERT INTO a_currency_exchange_gbp SET
AED='$aed_gbp',
AFA='$afa_gbp',
ALL='$all_gbp',
AMD='$amd_gbp',
ANG='$ang_gbp',
AOA='$aoa_gbp',
etc

INT Field Won't Go Beyond 127
Points INT(3) is a row in a table. I use PHPMyAdmin.

Each week, I have to update the numbers in the row, and this week some of the rows should read 146, or 140, or 136.

However, I enter the number - say 146 - but when it is saved, it turns the nuumber to 127. And numbers I have entered of more than 127 display only 127!

Help! In desperation, I changed the INT to INT(4) and then INT(5) and changed the name of the row. None of these affect the result, which remains at 127.

Is this a clear indication of insanity, or something a little less disturbing, please, Experts?

Add 1 To A Field
I have a int field. Each entry into the database I want to add 1 to this (i.e. 70,71,72). I've tried $therow = $row['entryno']+1, but that doesnt seem to work...

Get Field Value
I am using PHP with MySql. I have a statement:

$sql = "select * from Validnames where UserID = '$UID' && Location = '$Location'";

Am I correct this gets a single matching row of data from the database. If so, how do I pull out the value of 1 of the fields in the row. (2nd column)

Field Contains
How would I do a Select statement looking for a specific word or group of words in a long text field?

Select *
From centers
Where c_specs "Contains" 'The Words I'm Looking for'

How do I run that SQL command on a long text?

YES/NO Field
This is probably a very simple answer but I get an error when running the following SQL code:

SELECT * FROM news WHERE newsLive = "TRUE" ORDER BY newsPostedDate DESC

The newsLive field is a simple TRUE/FALSE - YES/NO field.

This is the error I get:

Expected end of statement
SQL = "SELECT * FROM news WHERE newsLive = "TRUE" ORDER BY newsPostedDate DESC" .

@var As Field
How to make variable value as field name?

I try: select @any_var from mytable;

WHERE Field
s there any performance difference btw the following WHERE criteria.

WHERE abcfield = 'XXXX'
vs
WHERE abcfield <> 'XXXX'

A)There are same number of records that for each of the above criteria
B) abcfield is indexed.

This has been puzzling me. At first i thought that WHERE abcfield <> 'XXXX' would be slower, but thinking abt how mysql uses indexes, it might not necessarily be the case.

Add To A Field
I have a field that I've stored as an array in mysql. I want to add to this field. Can someone tell me how I can do this?

Field_A = red,green,blue

I want to add "yellow" to field_A so that the record now reads: red,green,blue,yellow

Field Value As Field Name
is it possible to use the value from one table row field as the field name for another table, within one query?
ex:

select t1.fieldname from t1, t2 where t2.(t1.fieldname) = 'whatever'

Field Like And Like?
i search google for sql field like and didn't find anything about field like. i need to know what is the difference between field like and like

Field Name
is it possible in mySQL (or SQL generic) to use a field content as a field name?
Example:
I have created a database with a couple of fields which are named 1, 2, 3 and so on up to 14. It is a special calculation table.
Also there is a field called "reference" which contains a number between 1 and 14. This number tells me which field I should use for further calculation.
Does someone know a solution for this

Field To Use
I am a newbie in MySql and I am working on some tables which are required a 'url' field (internet address) . So my question is...which database field shall I use for that specific entry? So it shows up as a link when I am prosessing my data on a web page?

Same Field
I had a normal KEY INDEX on one VARCHAR field
I then added a FULLTEXT KEY to the same field.

I wonder if I should now remove the normal KEY INDEX
as obsolete or leave it? Can I expect the FULLTEXT index
to behave just like normal KEY index (if using without
the match operation)? Would there be some performance
difference in this respect?

Field More Than Once
I need to query a field more than once in an SQL statement using standard SELECT field and SELECT CONCAT(field), for instance:
Code:

SELECT
news.id,
news.subject,
CONCAT('<a href="',news.id,'">',user.name,'</a>')
FROM
table...

mySQL won't let me call the news.id field more than once, though. Can anyone help me? I thought the solution might be along the lines of SELECT news.id AS sql_id,CONCAT('sql_id',username,'...').. but this produced an error.

ID Of A Field
I insert a field using Insert tool, and I want to view its ID..
If I use LAST_INSERT_ID(), I can receive some bad ID's if someone insert data in the same time..
What function to use?

Value Of Field
I have a variable

$Variable_A= "Honda Ford Chevy Toyota BMW";

In my database I have field "Field_1" which has a value "Ford"
Now In my SQL select I want to pick record only when value of "Field_1" exists in $Variable_A

Particular Field
I will delete a particular value ( address to an image) in a field, but my code doesn’t work. Here is the code:

if(isset($tabort1)){
$sql="delete image from sidor1 where id="$pageid"";
$rs=mysql_query($sql, $conn);
}
The if statement execute and I get no error messages, but no deleting takes place. What am I doing wrong?

What Field Type
I'm using a credit system in my database

what is the best field type to use for numbers which will have values added and deducted.

CONCATing From The Same Field
Is there a way to query this table:
+------+-------+
| id_1 | value |
+------+-------+
| 1 | 5 |
+------+-------+
| 1 | 6 |
+------+-------+
| 1 | 7 |
+------+-------+
| 2 | 8 |
+------+-------+
| 2 | 9 |
+------+-------+
| 2 | 10 |
+------+-------+
Like this?
+------+-----------+
| id_1 | comma_del |
+------+-----------+
| 1 | 5, 6, 7 |
+------+-----------+
| 2 | 8, 9, 10 |
+------+-----------+

Using A Field From One Db In A Query Of A 2nd Db
I have a page that has an address something like:

[someserver.com?SID=23]

SID is the 'SID' field of the 'subject' table in 'database1'. I use the SID in a query to pull out some data and display from database1.

Within the subject.php page, I want to do a MYSQL query of 'database2' and display some of the data from it. One of the fields is 'CID' in the 'category' table. I don't want to include any data from 'database1' in this query, but since database1.subject.SID is equal to database2.category.CID, I figure I can use it to identify the 'category' in 'database2'. See Query 2 below. I realize that the problem is the 'WHERE c.CID=" . $_GET['SID'] . I don't know how to pull in the SID value from the URL to use in Query 2.

In the first query, I did this:

Query 1 (database1: tables- subject)

mysql_select_db("database1")or die("Could not connect : " . mysql_error());
$qry1 = "SELECT SID from subject";
$results = mysql_query($qry1) or die("Query failed : " . mysql_error());
$SID = $_GET['SID'];

I then echoed some information to display from database1, using SID as the identifier.

Query 2:

database2 (tables: item, bridge, category)

mysql_select_db("database2")or die("Could not connect : " . mysql_error());
$qry = "SELECT title,url from item i left join bridge b on i.ID=b.ID left join category c on b.CID=c.CID WHERE c.CID=" . $_GET['SID'] . "order by title";

I hope that is clear enough and that someone can give me some tips on how to do this. It is unclear to how to data use from previous queries and/or the original GET Request in the URL to do another query.

Using A Field Alias In Another Field

I'm migrating a DB from Access to MySQL and I having problems with queries similar to this: SELECT 1 AS x, x+1 as y; I get a "ERROR 1054 (42S22): Unknown column 'x' in 'field list'" error. Is there a way to do this without calculating 'x' twice?

I'm migrating a DB from Access to MySQL and I having problems with queries similar to this:

SELECT 1 AS x, x+1 as y;

I get a "ERROR 1054 (42S22): Unknown column 'x' in 'field list'" error.

Is there a way to do this without calculating 'x' twice?