MYSQL :: Updating Multiple Username In One User Field

Username And Passwd Updating
i have an excel list with 200 people, so what i do is convert the excel file to mysql, using a $24.90 sofware.....once i have the mysql databse
i add the column username and Password, so each people can access through a php login page enternig their username and a password and see their information in the database...
The excel list changes every 15 days...all the fields!...so the problem is the fact that this excel sheet does not comes with a username and password column....and since they may be more or less people on it user information will not correspond to the username and password rows that on the db

Displaying Username In In User Page
I've labelled my textfield name as "u_username" and after filling up the text and password fields, it will passthrough an authentication process, User.php:

// here i put the username into a variable
$username = $_POST['u_username'];

in the index.php, i've stated the code <?php echo $username; ?> but i tried out many times it just can't display the username. Is there something i miss out or i've done any mistakes?!!

Truncate Email Address In Username Field?
I imported a bunch of users using their email address as the username. I need to update every user who has an email address in the username field, to convert the username to just what is before the @ symbol.

Example:

user@host.com becomes user

and

user.name@host.com becomes user.name

Can anybody help with this?

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.

User Upload Updating Row
Im allowing a user to upload an image to my server picture folderthat is to be uploaded
and viewd in thier member profiles.As it is right now i can not get it to work 100% right
So i am using secind best for now and allowing avatar selections for profiles wich
works like a charm.

in my codes the upload works but it will not load the file path into the row designated
here is the code i been trying. Code:

Updating Multiple Tables
My webserver is running an older version of MySQL (3.23.56 ). I am trying to update a table based on criteria from two tables, and I can't seem to get the syntax quite right. Here is what I tried:

UPDATE (member, unit)
SET member.status_id = 3 WHERE member.unit_id = unit.unit_id AND unit.etime > 0

Does anyone know an older syntax to do this?

Updating Multiple Rows
The following UPDATE query works fine if run directly into phpMyAdmin (I take the $sql output of the script with the data in it and paste it into phpMyAdmin).

But it doesn't update my records if run from the PHP script. I can't seem to figure out where the bug is: Code:

Updating Multiple Rows
I need to update multipule rows a once, using PHP. Here's what I mean.

I have a mysql query out put the data as a form like this:

Item 1
<input type="hidden" name="id" value="1">
<input type="text" name="order" value="">

Item 2
<input type="hidden" name="id" value="3">
<input type="text" name="order" value="">

Item 3
<input type="hidden" name="id" value="4">
<input type="text" name="order" value="">
<input type="submit">

Order need to be updated. I could setup to do a loop with multiple queries.

Updating Multiple Rows
I am running the following UPDATE statement

UPDATE history
set t_own = t_user * 0.75
where user_cur = 7
and lg = 'Local'

t_own and t_user are defined as DECIMAL 12,2

650 rows should be updated, but i get

error 1136 Column count doesn't match value count at row 1.

Updating Multiple Rows.
I went to MySQL home page and found one comment that kind of tried to explain it but it was written in such a manner that I could make no sense of it at all.
I am trying to find a way to update a row with 12 entries in a table that has 12 teams (teamid) and 12 ranks (r1,r2,r3...r12) the data to update the teamid comes from a form and they would all be updated (unless everything remains the same) with the use of the said form.
I am not certain how to make this work (call the table 'team')

PHP Code:

UPDATE team SET teamid='team1', teamid='team2'...teamid='team12' WHERE?

Updating A Field
i using access and i have a table with two fields in it, "hit" , and "id", hit is a long integer, and id is an AutoNumber, is it possible to create an sql query, so that when it is run, selects a record using the id feild, and increments hit by 1?

Updating Multiple Rows In One Query
tried to find the answer with search but didn't return any answers.

OK, here is the table

table test
------------------------
| test_id | test_order |
------------------------
| 1 | 1 |
------------------------
| 2 | 2 |
------------------------
| 3 | 3 |
------------------------
I'm trying to change the orders in one query, but not sure how to do that.

phpMyAdmin shows me the code like this

Quote:

$sql =
'UPDATE `test` SET `test_order` = ƈ' WHERE `test_id` = 1;'
'UPDATE `test` SET `test_order` = Ɖ' WHERE `test_id` = 2;'
'UPDATE `test` SET `test_order` = Ƈ' WHERE `test_id` = 3;'
. ' '

I'v tried that but got a syntax error.

MySQL version is 4.0.26, can anyone help please?

Updating Table After Doing Multiple INSERT
I'm currently trying to do the following :

INSERT INTO sometable (userID,otherstuff) select ....blah blah ORDER BY RAND() LIMIT 10;

So I do that to get 10 random entries which works fine, however is there an easy way to then say...

For each of the 10 random userID's just inserted update another-field in another table with matching userID to the ones just inserted?

Updating Multiple Rows With Same Fields (in One Query?)
I have 2 tables here

table categories

+--------------------------------------- +
| cat_id | cat_name | cat_total_articles |
+----------------------------------------+
| 1 | PHP | 23 |
+----------------------------------------+
| 2 | MySQL | 17 |
+----------------------------------------+
table articles

+---------------------------- +
| article_id | article_cat_id |
+-----------------------------+
| 1 | 1 |
+-----------------------------+
| 2 | 2 |
+-----------------------------+
Now I've changed an article's category from cat1 to cat2, and I need to update cat_total_articles of both cat1 (minus 1) and cat2 (plus 1) in category table.

Is it possible to combine the following queries into one statement?

PHP

mysql_query("UPDATE categories
SET cat_total_articles = cat_total_articles + 1
WHERE cat_id = 2");

PHP

mysql_query("UPDATE categories
SET cat_total_articles = cat_total_articles - 1
WHERE cat_id = 1");

Updating A Text Field From A File
Is there anyway to update a text field for a given row from a file?
In other words I'd like to replace teh contents of a text field with the contents of a file.

Problem Updating Time Type Field
Here's a query I tried and doesn't work for me.

UPDATE activities SET ljTime=19:00:00 WHERE actID=05052003

I have also tried this:

UPDATE activities SET ljTime=07:00:00 [PM] WHERE actID=05052003

But I still receive the error:
[txoov] ERROR 1064: You have an error in your SQL syntax near ':00:00 WHERE actID=05052003' at line 1.

Timestamp Field Not Updating When Record Modified
I have a couple different tables ... all of which I am trying to use a timestamp field to track when records were changed. The problem is that when the records are updated the timestamps are all showing up as all zeros.

The timestamps are all set to "not null" and i have tried multiple defaults (CURRENT_TIMESTAMP, now(), time(), etc) with no success.

This is happening on 4 different tables in 2 different databases (all on the same server tho).

Updating A Database Field Based On A String Or Filename
I am using MySQL to store images that I serve up with Coppermine photo gallery. Each image is shown in low resolution format. I also store medium and high resolution versions of these files for download. There is a caption field where I put these links using BBcode. It looks something like this: Code:

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:

MySQL Multiple User
I am currently working on this simple project. There will be about 10 users for this intranet website. Here is a brief desc. on this project.
This Company makes this product that requires an assembly of many components. Now whenever someone puts an order for this product, the required items sometime is not available. So I want to create a MySQL database where users can enter this order no. + required components.What happens if two user try to alter that order at the same time? E.g

Order No.1 requires ItemA,ItemB,ItemC.
And if someone is altering that Order no.1 and someone else accidently try to alter that Order no.1 at the same time. Will it corrupt the data? What happens next?

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)

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.

Multiple User Connect To Mysql
want to develop a website using php and mysql. concept of the site is information storage.
would like to know if a user is currently filling up a form and another user logon to the site and wish to fill up some information. will the database will be locked? because someone is already connected to the database?

Prevent Multiple User Registrations
What's the best way to prevent someone from registering multiple accounts on my website? Should I create a column called "IP Address" in the members table and make it UNIQUE? Are there any complications with this?

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.

Granting User Access On Field With Restrictions Or Conditions
I am planning to develop a database that will have many users. Assume I have a table called Customer having these fields (id, name, user). Assume we have these Data:

ID Name User
1 Cust1 user1
2 Cust2 user1
3 Cust3 user1
4 Cust4 user2
5 Cust5 user2

I want the users to have access on those records that are created by them only or those that have their name on in the (User Field).

For example, if (user1) accessed the database and called: "SELECT * FROM customer;" it should return these data only automatically (or throw an exception at least):

Result:
1 Cust1 user1
2 Cust2 user1
3 Cust3 user1

(or throw an exception at least) that he cant call this statements without a where cluse like (USER=user1).

also, the user should have only the right to insert records having their name on in the (User) Field. For example: user1 can only call : "INSERT in CUSTOMER Values (6 , Cust6, user1), if he inserts user2 instead, the system should through an exception.

I hope I made clear examples of what I would like to build. How can I acheive this?

Finding Field In Table That Matches User Supplied Input
I've come across match/against, field, and locate. I've been unable to use these to match against columns.

As an example, I have a table with two columns, an index number and a domain name. The user supplies a FQDN (fully qualified domain name). I would like to compare all of the domainname rows to see if they match against the FQDN and return a go/no-go value.

The match function is close, but works in reverse. I cannot see a way to get locate or field to work with fields.

Multiple Values In One Field
Is it ok to store multiple values in one field? I have a site where users input Category Name and Category Index into a form. I want to be able to store both values in one field;

for example: 0|News or 1|Sports

The reason for this is long so I won't bore you, but I just want to know if I will run into problems doing this?

Multiple Field Primary Key
I've created a table using the MySQL Control Centre and set two fields to be a Joint primary key. At least that's what I was intending to do and what would have happened if it had been an Access database ( I'm well used to working in Access )

What I seem to have ended up with is a table with two sepatate field each marked as a primary key and each one preventing duplicate entries in the separate fields.

I think that If I'd used the command line SQL to Create the table
CREATE TABLE tblLinkPersonDept (
PersonRef MediumInt;
DeptRef MediumInt
PRIMARY KEY ( PersonRef, DeptRef );

Then I'd have got what I want. However, Is there a command line instruction to change the table I have to what I need ? Or does anyone know what I'm doing wrong in the Control Centre ?

Multiple Count From The Same Field
I have been having some trouble with the query below.

select a.advisor_ao as 'AO',
case c.existing_client when 'feps' then count(c.existing_client) else 0 end as 'total'
from advisor_tb a left join company_tb b on a.advisor_id = b.advisor_id
left join comp_seminar_ca_tb c on b.company_id = c.company_id
group by a.advisor_ao

I am not looking for an answer just wanted to know if my query is incorrect because if it is not thean it's my table structure

Multiple ID's In One Field
I have a program using PHP and MySQL, using categories. At the moment I simply have an 'cid' field with category ID in, which is obviously easy to use ("...WHERE cid = $id" etc).

But I now need to use multiple categories. The way I was looking to go would be to store the ID's like so... "1|2|4|8|45" etc, in the 'cid' column. I dont know how to extract an ID using MySQL. I could do it through PHP but it would be longwinded. Is there a built in MySQL function that is able to "pick up" the ID. I don't think "LIKE" would be suitable.. no?

Multiple Entry Field
I have a table for members and a table for trades and often a member will have many trades listed on thier record. There are only 2 columns in the trade table 'table_ID' (PK) and 'tradelist' (plumber, electrician, etc).

I have created an admin page in D/Weaver using PHP code, which you can add to the trade list as a new trade pops up and I have an add & edit member page with checkboxes that lists all of the trades and you can just check the ones that apply to that member.
Problem 1 - It only applies the first checked box to the database.

Multiple Field Index
In phpMyAdmin I can create an index that is made of two fields. If I create a filed that starts with a user id and the second part is an auto incrementing value how do I use that? Can I use it with a:
Quote: SELEST * from `table` where my_index like 'epanagio%'

Is this the reason combined indexes are created.

How To Update All Fields With A Multiple Of The Field Value?
Is there a MySQL command to replace all the values of a field with the field's original value that's had a mathematical formula applied to it? Basically, I want to divide the field's original value in half, then add 40% more. The formula would be y=x/2+.2x where y is the new value and x is the original value.

I know I could write a PHP script that would select each field's value, apply the formula, then update the new value, but it'd be cool if there's a MySQL built-in function to do this.

Concatenating Multiple Rows To One Field?
I got a slight problem with a web application, its supposed to several
entries for a certain ID, and display them as one string.

Example:

ID | Name
-------------
1 | Jack
1 | Peter
2 | John
2 | Mark
2 | Ellis

Expected Result for ID 1 should be "Jack, Peter" for example, or "John,
Mark, Ellis" for 2.

Least problem would be clipping off a trainling or leading ",", but the
main problem is a SELECT statement to get them all in one go. Else I'd
have to select every Name per ID, and concatenate them in PHP or
elsewhere. Given its around 100 IDs, I'd have to do like 100 querys to
get the names, *in addition* to the Querys I need before and after.

I want to avoid that, but I couldnt find a simple solution that works
in MySQL 4.1 and above, aswell as it mustn't involve Stored Functions
or something, because I got no access to the mysqld itself, to add the
funcs as modules. Code:

Deleting Multiple Rows Have The Same Field Value
I have 100 rows that all have the field vaule of 'XYZ' I would like to
delete only 10 of those rows.

Is there such a query like "Delete 10 rows from table where table.field
= 'XYZ'"

Multiple Values In Single Field
If there is an existing field with values in it (1,2,5,6) comma deliminated, what is the process for querying and pulling these out? Is there something that would be combined with IN that would convert the values somehow?Thank you for any thoughts, and please rest assured that I am aware of the ill nature of multiple values in a single field.

Combining Multiple Records Into One Based On A Key Field
I'm really scratching my head over this one. I'm working with CSV data exported
from Excel, which explains why it's a mess to begin with.

Within a table (or via any other means someone might be able to recommend) I
need to combine multiple records which share two like fields. (If that's not
clear, the real-world explanation is below.)

+----------+--------------+----+----+----+----+----+
| ID | ADDRESS | P1 | P2 | P3 | P4 | P5 |
+----------+--------------+----+----+----+----+----+
| COMP1 | 123 Main St | 1 | | | | |
| COMP1 | 123 Main St | | 1 | | | |
| COMP1 | 123 Main St | | | 1 | | |
| COMP2 | 45 Oak Pl | 1 | | | | |
| COMP2 | 45 Oak Pl | | | 1 | | |
| COMP2 | 45 Oak Pl | | | | 1 | |
| COMP2 | 45 Oak Pl | | | | | 1 |
+----------+--------------+----+----+----+----+----+

Must be distilled to :

+----------+--------------+----+----+----+----+----+
| ID | ADDRESS | P1 | P2 | P3 | P4 | P5 |
+----------+--------------+----+----+----+----+----+
| COMP1 | 123 Main St | 1 | 1 | 1 | | |
| COMP2 | 45 Oak Pl | 1 | | 1 | 1 | 1 |
+----------+--------------+----+----+----+----+----+

So essentially I want to combine all of the 'Px' fields for each ID into one
record.

The real model : The data represents companies (identified by ID) and the
product lines they stock (P1 - P5). Right now, there's a separate rec for each
product line. I need to combine those seperate recs into one.
____________________

I suppose I could import the raw data into separate tables, each with one 'P'
field -- and then combine them into another, but I'm not sure about that
approach either.
____________________

I already tried to make Excel combine before exporting, but had no luck.

Select Multiple Instances Of A Related Field
Is it possible to write an SQL statement to export mutliple instances of a related field in a single record?

For example, ContactsTable is related to ContactHistoryTable. I want to select the contact record and export 3 columns from the ContactHistory records containing the most recent ContactHistory.Type = Phone, most recent ContactHistory.Type = Appointemnt and ContactHistory.Type = Action

Combining Multiple Records Into One Based On A Key Field
I'm working with CSV data exported from Excel, which explains why it's a mess to begin with. Within a table (or via any other means someone might be able to recommend) I
need to combine multiple records which share two like fields. (If that's not
clear, the real-world explanation is below.)

+----------+--------------+----+----+----+----+----+
| ID | ADDRESS | P1 | P2 | P3 | P4 | P5 |
+----------+--------------+----+----+----+----+----+
| COMP1 | 123 Main St | 1 | | | | |
| COMP1 | 123 Main St | | 1 | | | |
| COMP1 | 123 Main St | | | 1 | | |
| COMP2 | 45 Oak Pl | 1 | | | | |
| COMP2 | 45 Oak Pl | | | 1 | | |
| COMP2 | 45 Oak Pl | | | | 1 | |
| COMP2 | 45 Oak Pl | | | | | 1 |
+----------+--------------+----+----+----+----+----+

Must be distilled to :

+----------+--------------+----+----+----+----+----+
| ID | ADDRESS | P1 | P2 | P3 | P4 | P5 |
+----------+--------------+----+----+----+----+----+
| COMP1 | 123 Main St | 1 | 1 | 1 | | |
| COMP2 | 45 Oak Pl | 1 | | 1 | 1 | 1 |
+----------+--------------+----+----+----+----+----+

So essentially I want to combine all of the 'Px' fields for each ID into one
record.

The real model : The data represents companies (identified by ID) and the
product lines they stock (P1 - P5). Right now, there's a separate rec for each
product line. I need to combine those seperate recs into one.
____________________

I suppose I could import the raw data into separate tables, each with one 'P'
field -- and then combine them into another, but I'm not sure about that
approach either.
____________________

I already tried to make Excel combine before exporting, but had no luck.

Creating A Single Field From Multiple Row Results
I know how to combine multiple columns to get a single result field but I'm not sure how to combine rows from the same column into a single result field.

Basically what I want to do is the following. If my table looks like:

risk | reference
-----|------
a | 1
a | 2
a | 3

I want a query that will give me:
risk | references
-----|------------
a | 1, 2, 3

Inserting Into Multiple Tables Using Auto Incremented Field
I'm inserting a record into MySQL 4.0 using Visual Basic ADO. When
using the AddNew and Update method I am unable to retrieve the value
of a Auto incrment field (Yes I know I can MoveLast but this icreases
the update time by a factor of 10 and when your talking about 200,000
inserts its way to long).

Is there a way to insert the record into
multiple tables in one statement where you can use the value of the
auto incremented field as the key in the second table? So the
statement inserts the record into a table retrieves the new
auto_incremented key and then insterts information into the second
table using the incremented key as the primary key?

Enforcing Unique Field Values Accross Multiple Tables
I have 2 tables (t1,t2) with unique keys defined in each. In addition to enforcing unique key values in each table, I would also like to enforce unique key values accross both tables (ex. If key value 'XXX' appears in t1 I don't want to allow entry of key value 'XXX' in t2 and vice versa).

Username?
I use the default mySQL username (root) in localhost and i want to upload it in my hosting's server using another username (e.g. : whutadilly) is it possible to do? or should i change mySQL username on localhost or what. i've read the stickied thread "Importing and exporting data with phpMyAdmin" but i wonder if its for different usernames.

Username And Pwd.
I have installed the database...and downloaded mysql administrator and mysql browser from this site.But I dont understand what username and password I need to enter?? I tried entering this site's username and password but it failed.

Username For Mysql
when I signed up to mysql, I got a root password
Which is what i use to sign in to mysql.
But,no user name.
( I know to use root but i would like to change it)
Does anyone know how to go about doing this or at least
point me in the right direction.

Username Question
I've got one little question, maybe a little silly, but where can I find my MySQL password? I've installed apache, PHP and MySQL on my computer, for learning PHP and MySQL, but to use MySQL with PHP I need the username of my MySQL account or something, but I don't know what it is and I can't remember I once set a username.

Seleting The Id Of A Username
Basically what I'm trying to do is make a delivery system, what the system does is the user enteres a name of a person, this then grabs teh users id and and inserts that id in to a database along some other details, that the mian bit I need help with the other bits I have sorted.

$invitedinfo=mysql_query("SELECT id,username,address FROM users WHERE username='$inviteid'");

Then what I want to insert is for '$inviteid' to be turned in to the id of the username which it is being paried up with at the moment.