MYSQL :: Add A Record To An Existing Recordset?

Inserting A Record Into An Exhisting Recordset
I have a two page form. The first page captures text information from the user, the user submits the form (which writes the information to a database). I then allow the user to upload up to 6 images, whose names id like to also add to the recordset. Let me give you some information on the table.
It has about 30 records (Name, Email, Phone,....) The last 6 records are (Photo1, Photo2, Photo3, Photo4, Photo5, Photo6) which should be the names of the uploaded images of which i'll use php to upload and grab the image name and store it in these variables ($Img1, $Img2, $Img3, $Img4, $Img5, $Img6)
Here's what I'm trying to have happen, once the images have been uploaded, id like to change the Image records only. The primary key for the recordset will be ID, So I think something like this will be used?
INSERT INTO Providers VALUES
'Photo1' =='$Img1',
'Photo2' =='$Img1',
'Photo3' =='$Img1',
'Photo4' =='$Img1',
'Photo5' =='$Img1',
'Photo6' =='$Img1'
WHERE 'ID' == '$ID'
Is that right?

Adding To An Existing MySQL Field/record
I want to be able to add to an existing record.

I have a table call pages, and within this i have a column called audit_history.

How can i run a query so that it adds content (stored in $newHistory) to the existing audit_history field. I don't want to have to retrieve the audit history first and then add it on using PHP as this seems a waste.

Once the query has been run the audit_history should contain the existing data and the $newHistory data.

Table Joins On Existing/Non-Existing Data
I have a question that involves a joining tables on potentially non-existing data, but we will know the expected values whether they exist or not.

Say we're keeping statistics on any given number of sports for attendances over weekly time periods. There's two tables to hold data.

------------------------------
TABLE: StatisticTypes
id
name
------------------------------
TABLE: Statistics
id
statisticTypeId
value
date
------------------------------

Then, say, we have three entries in the StatisticTypes table for types of sports we're keeping data on...

Recordset
Not sure I'm in the right forum, but anyway:
Is there a way of updating a recordset of db in access, but not to update the database itself?
I'm using asp pages.
To be clearer: I need to update a certain field in a database,
just as long as the asp page is accessed, but I don't want this update to take effect when a user is out of this asp page.
So I thought of creating A RECORDSET and update there this certain field in part of the records, but want no effect on the real db.
Btw - For what u r about to propose. Do I need a write permission in the database, if not really updated?

Recordset Error
I just installed MySQL on Windows 2000 Server, and am trying to rewrite my ASPs to interface with it. When I try to access one of the columns in the recordset, Microsoft Script Debugging pops up with an "Exception Occurred" error. In the command window I type in, and receive back, the following:

? objrs("FilePath")
[error]

? objrs("ImageType")
Reg

? objrs("FilePath")
G:/Documents and Settings/disk42/Desktop/analog.txt

ImageType happens to be the column right before FilePath. I can't think of any reason why I can't access FilePath until after I've accessed ImageType. Anyone else run into this problem, or know what may cause it?

Recordset Length In PHP
I have the following peice of code in a PHP page:

while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
$categoryName = $row[1];
$categoryDescription = $row[2];
$pos = count($aryCategories);
$aryCategories[$pos][0] = $row[3];
$aryCategories[$pos][1] = $row[4];
$aryCategories[$pos][2] = $row[5];
$aryCategories[$pos][3] = $row[6];
}
if (count($aryCategories) == 0) {
header('location:directory.php');
}

Is there a way I can test the length of the recordset to trigger the redirect, rather than building the array (or not) and testing the length of that. Something like:

if (rs.length < 1) {
//redirect
} else {
//while loop
}

Also, while you're looking, is there a way I can set the $categoryName and $categoryDescription variables before the while loop? These values are the same for every row returned in the recordset so I don't need to reset them to the same value every time in the while loop. Something like:

if (rs.length < 1) {
//redirect
} else {
$categoryName = firstRowFirstField;
$categoryDescription = firstRowSecondField;
//while loop
}

Clean Up Recordset
I have a basic query that returns a list of data. My problem is that the query is returning multiple copies of a given "t.clinical_specialty". This makes sense, because there may be more than one "s. course_ID" that matches up. How do I change my query so that only one copy of any given "t.clinical specialty" appears in my list? Code:

SELECT t.clinical_specialty
FROM clinical_specialty AS t, course_status AS s
WHERE t.course_ID = s.course_ID
AND s.status = 'online'
ORDER BY t.clinical_specialty ASC

Table/recordset Problem
After building a form in php the MySQL table I was adding to quit accepting data entry on the 16th record. Now it is stuck however I was able to edit records. If I delete and rebuild the table it won't accept any new record entries.

Not sure why this is? I recently shifted from ASP to PHP and an apache server and kind of relearning everything.

To Omit Records From A Recordset
Does anyone know how to configure a table so that you keep all your data in the database but perhaps omit certain records from showing on the website? Like whether the row in the table is an active or in-active row.

Update A MySQL Recordset Through ADO?
How do I force an update to a MySQL recordset through ADO on an open form?

recordset.update does not appear to be working for me. Here is why:

I am using MS Access to build forms and ADO to populate the controls on that form.

I want a user to be able to edit data on a form, move to a new record and then move back again in order to find the record has been updated without having to close down the form.

If I set the ADO connection object to "currentproject.connection" (with linked tables) everything works fine. However, it is when I change the connection object to a full MySQL connection string that I cannot seem to dynamically update the database through a form.

Does anyone have any ideas?

Recordset Paging - Putting It All Together
I have just created a library database which includes 8084 books, publications, periodicals, etc. My client would like to see this database searchable in various ways which I am now able to do all by myself (slow learner) but I'm now to the point where I'm trying to put all the bits and pieces together for a good recordset paging system.

I understand that I will probably first use mysql_num_rows to retrieve the number of rows that will be resulted from the search. Then I think I have to use LIMIT to specify how many rows to display on each page of results (lets say 10).

I can use the offset with LIMIT so that my first query would be to select the first 10 (ie. LIMIT 0, 10) then another to select the next 10 (ie. LIMIT 11, 10)... but with 8084 records how will I know how many queries to build?

And how does one create the links where it shows how many result pages there will be on a query? For example this site uses exactly what I'm looking to do with each thread... it shows how many pages there are and they are linked to the next page, x of y pages, etc.

Is there something out there that will help me put this all together? Could someone kindly give me a kick-start?

How To Use The Ado Recordset Function, Addnew
After connected, and opened a recordset, the table has a primary key;
I want to add a rowdata, use the addnew,

like that
m_pRecordset->AddNew();
then a error happened, i dont know the reason.

Recordset Handling In MySQL/PHP
I am using PHP / mySQL to write a Quiz. I currently load all the questions into a recordset. I then, iteratively, call this page, displaying the first question and possible answers, then the second question and possible answers and so on.
While this works fine it is obviously dead ineffecient - I load the recordset with the same set of questions each time. Also, the questions come out in the same order each time.

I know you can use the LIMIT clause to get a record / n records at a time and you can also use RAND() to mix the records.
Ideally, what I would like to do is one select statement with a RAND() which returns all the questions in a different order time the quiz is started. However, once the recordset is loaded, I want to use it and get the next question on each subsequent call of the file.

Updating A JOINed Recordset
Are there any tricks in updaitng a JOINed recordset?
I joned to tables and when I try to change a field on the recordset and
update it, I get this error:
"Unknown column 'CCDE' in 'where clause'.
CCDE is a field from one of the table and is not a field I am updating.

ADODB.Recordset.1 Error '80004005'
My database completely works in Dreamweaver MX at a local testing level,
but as soon as I upload and access my web pages remotely I get this error on
the browser.

ADODB.Recordset.1 error '80004005'

SQLState: IM002
Native Error Code: 0
[INTERSOLV][ODBC lib] Data source name not found and no default driver
specified

/index.asp, line 5

Multiple Queries In One Recordset/connection
I am doing an insert, for which I want to get the auto-increment value back as a recordet in the same connection, I have the following:

$query = "";
$query = $query . "INSERT INTO ";
$query = $query . "myTable ";
$query = $query . "(name,summary) VALUES ";
$query = $query . "('" . urlencode($_POST["frmName"]) . "','" . urlencode($_POST["frmSummary"]) . )";
//echo "Query: " . $query;
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

I want to add to the query:

$query = $query . "SELECT DISTINCT @@Identity AS 'newId' FROM myTable";

When trying a semicolan seperator, I get an error (this is also true in my query analyser - it doesn't work there either.) Is this even possible, and if so, what am I doing wrong?

Recordset Paging With MySQL & ASP Using LIMIT
I have an asp page which I want to display 10 records with a
'Previous' and 'Next' link underneath for navigating through the rest
of the recordset.

I have the page displaying the first 10 records and the links. The
links work, but the data does not change.

How can I adjust the SQL so that the code re-runs the SQL ? Is there a
way to override the LIMIT or change the way it is written ??

Code:

MySQL Command Issues With An ADO Recordset
I know you will probably say that I should use MS SQL Server DBs with ASP,
but my MySQL and ASP combo works and is affordable for me and up until now
didn't give me any grief until recently.

In essence, the following MySQL query commands cause my ASP to generate a
Type Mismatch error when I try to response.write the result of the query
fields:

Query 1

CONCAT(PICFILEWIDTH,' x ',PICFILEHEIGHT)

I call this query result into a dynamically-created array from a recordset
object, ie arrSQLData (the array) = oRSv.GetRows() (the recordset object),
but when I call say arrSQLData(0,0) it generates the type mismatch.

This used to work fine in the MySQL v4 something that had the crappy
installer, but since they made the new installer (and some other mods) it
causes this problem.

I've checked the MySQL site and it says that the above, which is an int, ' x
', another int is fine, but something in ASP doesn't like the result it
sends back.

Done a straight query from SQLyog and that displays it OK.

Query 2

IFNULL((sp.RRPTAX/sp.RRPNET),'0.00')

same again this generates a type mismatch when it used to work fine.
Obviously the command puts in 0.00 if the tax and net values are null, but
this doesn't work now. It generates a type mismatch.

Has anybody had this problem and fixed it?

Empty Recordset When Querying A Decimal Field
When I query decimal fields in my tables (i.e. prices and costs), nothing is returned to the recordset. If I change the data type of the fields to varchar or all data types, it's fine. Please can you tell me where I am going wrong, or if there other similar data types that I can use that will give me two decimals.

Have My Recordset Table Show Up In Random Order
I have the following code on my PHP page and my goal is to have the recordset show up in a random order each time the page is visited. I have tried a ORDER BY RAND() and come up with errors only. Is there anyway to find out what code I need to use and where I would place it in this piece of code? Code:

Invalid Distinct Recordset Returned On An Indexed Column
I have a table with about 1.2 million records. I have an index set on a column.

For close to two years, this query has worked perfectly fine:

SELECT DISTINCT `Mgmt_Area` as thevalue, `Mgmt_Area` as valueid from qcdata ORDER BY thevalue

Note, this is a programmatically generated query based on some user selections. This query actually is used to create a listbox. The index has 62 separate values.

This query would usually return: Code:

Looking Up Duplicate Record Or Adding New Unique Record
CREATE TABLE tbllayer (
LayerID int(11) NOT NULL default '0',
LayerSize int(11) NOT NULL default '0',
IceTypeID int(11) NOT NULL default '0',
Fingerprint char(16) binary default NULL,
PRIMARY KEY (LayerID),
UNIQUE KEY Fingerprint (Fingerprint),
KEY IceTypeID (IceTypeID)
) TYPE=MyISAM;

We have an internet monitoring application which stores objects in the above
table, with the fingerprint an MD4 of the object. In general about 30% of
the time an object monitored is already in the table, in which case we don't
want to re-insert it, we just want to find out it's ID. The percentage may
vary between 10% and 50% though.

Currently we have a cache in our application which works like this:

- object is monitored and its fingerprint taken
- is the fingerprint in the cache? if so, take its ID from there
- if not, do a SELECT on the table - if a match is found add it to the cache
and use its ID
- if not, INSERT the record into the tablem use its ID and possibly add it
to the cache too

Ok. In general, is it better to:

- do a SELECT to see if the record exists and if not INSERT it
or
- do an INSERT, and if it fails then do a SELECT to locate the record

What about if the duplicate ratio is high or low?

Automatically Record Date Of Record Entry
I have my database table set up and I have an HTML form that is PHP driven that will add the information entered into the form into to my database table.

I have a local buy-sell-trade Website. The way it has worked is that people fill out a form and the results emailed to me. I then take the information and enter it into a Web page. I only want the ads to be displayed for 30 days. I keep the ad for a total of 6 weeks (displays for 30 days and sits in limbo for 2 weeks afterwords) and if not renewed within that 2 week limbo period - I delete it.

Entering all the ads and keeping up with the dates manually has become a burden. I've only recently began looking into databases. My hosting company provides me with phpmyadmin and mysql 5.0. I'm new to all of this but I have managed to set up a database table and a HTML form that is PHP driven that allows ads to be automatically added to the database table.

There is a lot I need to do to make this ideal, but one step at a time.

First, I need to know the date (March 02, 2006) the ad was created or added to the database table. I know that I need to add some piece of code to my php form to record this information, but what code and where do I put it? I know I will need to create an extra field in my database table to house the date - I can handle this.

I've read the date and time information here http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

but there's a lot there and I don't know which is right for my needs. Plus, it doesn't tell me how or where to insert it into my php form (or does it?).

Getting Record Before Or After The Record That Meets The Criteria
In a query I want to get all the results and then order those results by last name, but then I want to filter those results down to only the record that comes before or after the record that has 'empno' = '1259'.

I want to get all the results already ordered and then filter them down to one record either before or after (depending on what is needed) the record where 'empno' = '1259'.

My.cnf Not Existing
this is my first posting here so plz do not flame me too much if my quest=
ion=20
marks me as a total noob.

My problem is, i play around a bit with mysql (4.0.13) and want to test t=
he=20
Replication. The Documentation at http://www.mysql.com says i have to do =
the=20
following:
Stop databases - done.
Build tarball of data and bring it on slave - done.
Modify my.cnf - ???

Here is my problem. There is no my.cnf existing on my machine. Maybe its=20
called different with the newer version? Or can it be considered empty an=
d i=20
have to build a new one? But if i have to, in which directory would i do=20
that?

ON EXISTING Clause
What I'm trying to do is update a table of mine in mySQL. However, some of the values may not exist in the table yet, while most will already be there.

I have done some searching and I think I need help. I'm assuming UPDATE won't simply act as an INSERT if the existing value is not found. From my searching it seems I need to use the ON EXISTING clause with an INSERT. However, it seems that ON EXISTING can only be used when there is a primary key. In my case, thats not the case here.

However, for me this is query confusing at the moment. Can someone provide a hand?

My table is called "rankings" and has the following structure:
comp_id - int (and an index)
uid - int
networth - int

How To Add A New Row To An Existing Table?
I am able to create database, tables, inserting values, and so forth. My question is this:

Let's say I have a table on the list of items I owned, and some time later, I need to add a new item between after row 12 and before 13. How do I do that? I did look at MySQL 5.0 Manual and didn't find anything similar to what I wanted to do.

Check For Existing Row ?
How do I determine if a row already exists ?

// see if a row has id=100

$id=100;
$query="SELECT * FROM mytable WHERE id=$id";
$result=mysql_query($query);

...what do I check now ?

Using Existing Database
I am setting up a new dev. machine and need to get an old database into the new installation of MySql. There are two databases showing on the database list 'mysql' and 'test'.
Can I drop the old database (contained in a folder) straight in with the existing databases and get any sense from it?

Existing Database
I'm using MySQL 4.1.9-nt & VC++ 6.0 .
how to get all the database names and table names?

I'm using the VC++ syntax,

CRecordset rs(&db); //here db is object of CDatabase class.
rs.Open(AFX_DB_USE_DEFAULT_TYPE,"show databases ");

Information From Existing Db
Dont know much about mySQL, so excuse my potential ignorance here :
Is it possible for a table to write information to another table automatically?
For example, if on "table1" I have a column called "code", can I write the entire contents of a specified row to another table called "abc" if the code "abc" appears in the "code" column of "table1"

Record Creates Record
I have a table that holds inventory information...primary key'd with an asset#. I have another table that I hold certain information in that I don't want in the main inventory table. I would like the second table to key itself and automatically enter a record when a device is entered.

For example, I enter a scanner in my main table, but I want the DB to then also enter that assetNumber into the second table for me.

Using Update To Add Value To Existing Numeric Value
I have users inputting hours into a mysql database. Is there a way to have a mysql statement take my input and add it to the existing value in a record? I suppose I could just select that value and use php to add the 2 values but was thinking mysql might have a function to do this for me.

How Do I Add Default To Existing Column?
It should be easy but i've searched from mysql manual to here...

Moving Existing Fields Out
Can someone tell me what would be the best way to move fields out of an existing table into new tables?

Best Way To Get Data From Existing Database
what I'm trying to do is get data from an exhisting oracle database that is not looked after by myself but I have access to. What I want is for my MySql database to mirror the oracle one or update every 5mins or so. I have done a quick search but as I have only made simple databases before I'm not sure what to search for!

Attaching An Existing Database
I would like to attach an existing database (I have the files from another system) to MySQL running on a recovery machine - yeah, the Linux system got screwed, but I managed to recover the files.

So, having the database files (.myi, .myd and .frm), how do I go about incorporating them into the current system? (Like "attach database" in MSSQL)

Foreign Key To Existing Table?
Is it possible to add a foreign key to a table that already exists? Or do I have to drop the table and recreate it?

Insert Row Into Existing Table
I have a table with rougly 70 threads, I now need to go back and insert a couple new entries into my table, but they cant go at the end, they need to go in the middle, say like ID 40,41,and 42. How do I do this?

Insert A Duplicate Of An Existing Row
I have a table with an auto-increment columns (absid) and I want to take a row with a certain absid and re-insert it as a new row with a new absid (but all other columns as from the copied row). Running mysql 3.23.55. Might something like the following work?

insert into mytable (absid,*) values 0 select * from mytable where absid=20;

or something similar? With 3.23.55 I suspect it won't work anyway but I have no access to a version 4 installation and wouldn't want to upgrade if what I want to do is inherently not possible.

Accessing Existing DB From Files Off A CD
I inherited a project from another developer who was fired. There is no database documentation. There is both an SQL backup file and the original database files on a CD. I have recreated the db from the backup file and that worked perfectly. However, I don't have full confidence in the accuracy of the backup file. I was wondering if I can somehow access the db from the files on the CD.

I copied the files to a folder on my machine and then changed the MySQL data folder setting to that folder. Unfortunately MySQL wouldn't start. What would I need to do to be able to have MySQL use the files copied from the CD as the data files?

Running An Existing SQL File
I hope this question is not too 'newbie' to answer.. I'm reading a book (PHP and MySQL web development by Luke Welling and Laura Thomson), and am getting an error on an example script.

I'm trying to create tables using an existing SQL file through MySQL. According to the book, it should be done like this:

> mysql -h hostname -u username -D database -p < file.sql
(I've changed the hostname, username, etc.)

However, I get the same error over and over again: ERROR 1064 (42000): ... at line 1. The book didn't even mention where to put the file, I've tried all sorts of things, but can't get it to work..

Copy Of An Existing Table
MySQL: How I make a copy of an existing table in phpMyAdmin or SQL ?

without copy data
with copy and data

Uploading Existing Database
I just got a dedicated server and can't upload some of the larger databases. I have tried compressed (gz) as well as the none zipped and automatic. None works for a 50MB + database.

I am not sure what's wrong, maybe it times out, but then I never get the error. If that's the case then how do I change the timeout settings in MySQL. Also I am using Plesk, not sure if that matters.

Edit Existing Data
I have just started to learn MySQL and this question might be silly. I have a MySQL table with user information. I want to edit this table in a website. I need to load existing data in a HTML form and then edit the fields and update them.
I searched the net but could not find anything .

List Of Non-existing Users
I have 2 tables: tableFrom , tableTo.

I want to have the list of users that ARE in tableFrom, but NOT in tableTo.
The common field is "username".
I want all the rows from tableFrom as long as they don't already exist in tableTo.
An important point is that we use MySQL 4.0.20, so I can't use "NOT EXISTS(...)".
This query needs to be pretty fast since it might be ran many times an hour, even minutes.

Check Existing Values
In my web site I'm using PHP, & MySQL.
There its needed if a user is trying to be registerd using a existing user ID.
That is I think, it should check such a value existing and if exists it must display "Existing User ID".

Convert Existing Data
i am working on a project and i need to convert already existing data into the mysql date format. I was told for the project that the date format was necessary. my first question is what is the advantage of having the mysql date formate which goes like this yyyy-mm-dd. the format my data currently is in is like this mm/dd/yyyy. The second question is how to convert from mm/dd/yyyy to yyyy-mm-dd