Update If Duplicate Found

Update Affecting Only First Found Row
I'd like to write a query along the lines of

UPDATE table
SET field=value
WHERE status=AVAILABLE

Now...let's pretend that there are 4 rows that have status available...how can I just have it affect one row only?

---

Update If Duplicate
does anyone know how to do an update on a table only if there's duplicate entry in this table using a query?

---

ON DUPLICATE KEY UPDATE
i have query to set a login time, or update the time if a user is already on the database.
$query = mysql_query("INSERT INTO logged (username,time) VALUES ('$userid','$time') ON DUPLICATE KEY UPDATE time='$time'") or die(mysql_error());

and the erorr i get back is 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 'ON DUPLICATE KEY UPDATE time='1110251002'' at line 1 im running MySQL 4.0.23-standard.

---

On Duplicate Update
I have tried to use the INSERT...ON DUPLICATE KEY UPDATE in Coldfusion. After I run the query the database seems to have the correct entries in it but Im getting an error message:
Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][MySQL][ODBC 3.51 Driver][mysqld-4.1.11-nt]Duplicate entry '0-thisURL' for key 2
<cfqueryparam value="#now()#" cfsqltype="cf_sql_date">)
v>

Am I way off the mark here? Like I say the entries in the database seem sorrect but I still get the error.

---

DUPLICATE KEY UPDATE
Like many tables, mine has a PK with AUTO_INCREMENT set. I would like to roll my UPDATE and INSERT sprocs together into one to keep things clean.

I'm having a hard time getting my head around how this 'ON DUPLICATE KEY UPDATE' works without checking for a null PK or -1 or something like that.

In other words, if I'm inserting new data, I don't have the PK value and my table has it's PK column as AUTO_INCREMENT, so how do I handle that? Pass it NULL for the PK on INSERT and a valid PK on UPDATES?

In the past I have done this with an IF ELSE block to check for existence, but the MySql docs lead me to believe this is built in somehow.

---

On Duplicate Key Update
The table has an autoincrement field that is the primary field. It also has id, date, and course_number that are indexed fields. Since the ON DUPLICATE KEY UPDATE how will I Insert and on duplicate update? The ON DUPLICATE KEY UPDATE works only when the field is the primary field. Will it work if the field(s) are indexed?

---

Insert On Duplicate Key Update
Is it possible so do something like this

"insert * from TempTabel on dublicate key update 'all fields'; "

So I don't have to write every column name. I need this, so I can use the same code on multible tabels. I could use replace, but then the foreign key in other tables are not updated.

---

ON DUPLICATE KEY UPDATE Syntax
I have two tables with identical columns. I want to update records in tbl1 with record information from tbl2.Code:

INSERT INTO tbl1 SELECT * FROM tbl2 ON DUPLICATE KEY UPDATE SET tbl1.col=tbl2.col, tbl1.col2=tbl2.col2

I seem to be missing something here...

---

ON DUPLICATE KEY UPDATE And Query Cache
After bludgeoning my head against the table for a bit, I figures out what the problem was that had been plaguing me.

When using a INSERT INTO ... ON DUPLICATE KEY UPDATE ...
statemtent, it seemed like the update was not taking place.
After viewing the webpage in question, (and doing the manual select statement), the old data was being returned.

I couldn't figure out why, until FINALLY, I added 'RESET QUERY CACHE' manually after the statement. Then the correct updated info was returned.

Apparently the query cache is not flushed after using the above ON DUPLICATE KEY UPDATE statement, like it does with other insert or updates.

What do I do now. Just not use that statement? Id like to avoid locking tables if possible. And I cant have the website showing the outdated info.

---

URGENT: Please Help Me With My ON DUPLICATE KEY UPDATE Query
Could you please help me do this quickly.

I have a query:

Quote:

mysql_query("INSERT INTO products (id, title, brand) SELECT id, title, brand FROM prodse WHERE approved=1 ON DUPLICATE KEY UPDATE groupname=a")

What I need it to do is that whne title and brand matches, it is then a duplicate listing. I have set up the unique key for this. When a duplicate listing is found it will then change the groupname value from "1" and update it with "a".

When it then finds the next duplicate listing that has the same title and brand as the duplicate listing that had it's groupname changes to "a", I need it to be change to "b" this time.

So I now have two listings that have a groupname of "a" and "b".

Now when there is more duplicate listings that have the same title and brand as "a" and "b", I now need the groupname to be updated to the actual listings id and not use "a", "b", "c", etc...

How can I do that.

I know how I can do the first one so that it updates to 8, but then how would I do the 2nd and 3rd ones.

Please help, I have to do this urgent as my database is really slow and it is damaging my site as it is taking about a minute or so to do each query.

Also, I would prefer it if it could be done in one single query. I can also use variables in the query and if statements plus other php like

Quote:

mysql_query("INSERT INTO products (id, title, brand) SELECT id, title, brand FROM prodse WHERE approved=1 ON DUPLICATE KEY UPDATE groupname=$groupname")

---

Insert On Duplicate Key Update Question?
I have UNIQUE comp keys with 3 columns. I want to insert new rows if the data doesn't match one of the 3 columns. If they match, don't do anything. I am trying to do it with one query without doing select first and bunch of comparsion then either insert or update depending on the comparsion.

I am think of using this:
INSERT INTO mytable ('col1', 'col2', 'col3') VALUES ('mydata1', 'mydata2', 'mydata3') ON DUPLICATE KEY UPDATE col1=col1, col2=col2, col3=col3

is it going to work? I think it first tries to insert my data as a new rows if it is not duplicated. If duplicated, it will update the data with its old data. But update will ignore the operation since it's the same data.

---

Using ORDER BY With UPDATE To Resolve The Duplicate Key
I found this page, which is relevent to my problem:

http://www.mysql.com/news-and-events/newsletter/2003-09/a0000000224.html

I followed the suggestion and altered my query to this:

UPDATE library_categories SET lft = lft + 2 WHERE lft > 1 ORDER BY lft DESC

and I get this 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 ' 1 + 1, + 2)' at line 1

Is there a way to get this query to work?

---

MySql UPDATE With Duplicate Rows
I'm trying to run an update on a linking table, the update is running into a Primary Key
constraint violation, and in my workaround I've got stuck trying to write a DELETE statement. Here's the table I'm working on: Code:

---

ON DUPLICATE KEY UPDATE With An Old Version Of MySQL
ON DUPLICATE KEY UPDATE is new as of MySQL 4.1.0, but the web server
that I'm using has MySQL 4.0.24

How would I implement ON DUPLICATE KEY UPDATE with an old version of
MySQL?

---

LOAD DATA INFILE Or ON DUPLICATE KEY UPDATE
I want to use LOAD DATA INFILE to load a text file into my database because of the speed at which it can complete the import.

But I don't think I can use it so I want to check here first before I completely nix the idea of using it.

Is it possible, using LOAD DATA INFILE and possibly the REPLACE argument, to retain values from specific columns in the row that it's updating. It says in the manual that "You cannot refer to values from the current row and use them in the new row" but goes on to say something about using SET that I don't understand.

Is there another way to use LOAD DATA INFILE and to retain values from specific columns that aren't being updated by a field in the text file?

(i hope some of that makes a little sense?)

If it's impossible to use LOAD DATA INFILE, how can I speed up my ON DUPLICATE KEY UPDATE query. Right now, it just processes 1 row of the pipe delimited text file at a time:

---

Update Values On Inserting Duplicate Index
I am very new to mysql. I have a question about using the "on duplicate
update" clause with insert command.

my table "data" has two columns, field1 and field2, where field1 is the
index and is "unique".

when I run

insert ignore into `data` ( `field1`,`field2`)
values
(1,2)
(2,6)
(2,9)
(5,1)

I got
1,2
2,6
5,1

because the third value pair, (2,9) was ignored due to duplication in
field1.

here is what I want to do: when inserting new record with duplicate
index, I want the existing record gets updated. in the above case, I
want the output look like the following after the insert command:
1,2
2,9
5,1
after searching mysql online document, I only found the "on duplicate
update" clause can do something similar, however, all the examples only
show setting the duplicate record to somthing unrelated to the new
values.

---

ON DUPLICATE KEY UPDATE - Don't Have A Key But Want To Update
This may seem like more of a PHP question but I will come onto the MySQL part in a minute.

Basically I have a PHP script that lets users upload 2 different CSV files to different tables. The problem I have is that sometimes these CSV files are updated and then need to be reimported into the database through the script. I have made this script totally generic so that when you change the table that is being entered to the rest of the script will alter itself to import the correct CSV to that table. But a problem arises when I have a CSV file that contains each product sold and the shopper who bought the product (so the shopperID may appear in more than 1 row) - however this file does not have a unique id for each row meaning that when I use an INSERT INTO with a ON DUPLICATE KEY UPDATE... it will just insert more rows instead of updating rows because there is now key in the table.

The format of the table is like this (with some example data to show you more clearly):

+-----------+-------------+---------+------+--------+------+
| ShopperID | Product Code | Product | Price | Amount | Units |
+-----------+-------------+---------+------+--------+------+
546733 1315 prod 1 64.00 64.00 0
546733 1316 prod 2 43.99 43.99 0

OK just ignore the last 2 fields - that is a problem with the old system there was which does not add the units up right.

Anyway basically I might have the same shopper ordering 2 things which will come up on different rows - I have 3 things I cannot do:

1. I can't make the ShopperID field the PRIMARY KEY because I have many times where there are 2 rows with the same shopper
2. And I cannot just do an UPDATE when it gets to a duplicate entry because it will overwrite every time it gets to a shopper that already exists.
3. I don't want to add a unique primary key to the CSV as this will mean that the system I am creating loses its ability of being automatic because the person (an admin) that uploads the file will have to make sure that it has that extra field which is auto incrementing.

Does anyone have any ideas on how I can get round this?

Here is my PHP function which contains the query:
PHP

// this function for insert data to csv
function makeINSERTS($text, $table, $tablefields, $correctcsv){
    $insert = array(); //make array for hold data insert
    $i = 0;
    $success = true;
    
    while(list($key, $val) = each($text)){
        // Insert the data
        $insert[$i] = "INSERT INTO ".$table."  VALUES('";
        $insert[$i] .= implode("','", $val);
        $insert[$i] .= "') ON DUPLICATE KEY UPDATE ";
        foreach($tablefields as $k=>$field){
            $insert[$i] .= "`$field`='{$val[$k]}',";
        }
        $insert[$i] = substr($insert[$i],0,strlen($insert[$i])-1);
        // echo $insert[$i].'<p></p>'
        $result = mysql_query($insert[$i]);
        if(!$result) {
            echo 'FAILURE to insert/update any or all of the database form uploaded CSV!<br />'
            $success = false;
            die('Query failed: ' . mysql_error().'<br />');
        }
        $i++;
    }
    if($success == true && $correctcsv == true){
        echo 'SUCCESS in uploading the CSV file to the database!<br />'
    }
    else {
    }
return $insert;
}

---

Update INT UNIQUE Column - Duplicate Entry Error
As an example, I'm creating a table which holds following values in MySql 5.0:

idcol lft (unique)
1 1
2 2
3 3
4 4

-> when I try to update the lft column using :

UPDATE unique_example
set lft = lft +1
where lft > 1;

I get following error message :

Duplicate entry '3' for key 2

Exactly the same approach works using SQL server 2000. Apparently, MySQL processes the rows one by one, as it throws an error on the UNIQUE lft column from whom I wish to update all rows at once? Code:

---

Update A Record Out Of 2 Duplicate Records Which Dont Have Primary Key In Mysql Table
how to update a record out of 2 duplicate records which dont have primary key in mysql table?

---

If Entry Not Found
I want to query my data base for a certain user and id and I want to display a message user not found if the entry isn't there or display the entry if it is.

---

Mysqladmin Not Found
I have been trying to get mysql installed on a Fedora 3 box for the past week.

I have installed the mysql 4.1 rpm and installed it the system. however I can not use any mysqladmin comands, nor can I shutdown or restart the deamon.

mysqld is running in my processes, and I can connect to it with the url. However I have no idea if its worring correctly, I assume not because I dont have any mysqladmin commands.

---

No Tables Found
I've recently set up a local test server on my new Mac under PHP5, MySQL 5 (5.0.24-standard), Apache 2, and phpMyAdmin - 2.8.2.2.
When restoring a database from the raw data files from my live remote server's mysql data dir, phpMyAdmin no longer sees any tables in any of my local databases. Instead, I just get the "No tables found in database" message below the database pulldown.
Note that the pulldown correctly lists the number of tables next to the database name (e.g. "myDatabase (5)") in the pulldown. Also note that my local sites work with the updated data, and I can see the tables in MySQL Admin. This seems to be a problem only with phpMyAdmin. Rebooting or turning on register_gobals did not work.

---

PID FILE NOT FOUND
mysql has been pissing me off today.... ive spent hours trying to solve the problem.. but what I can get it down to is that mysqld daemon fails to start, thus the mysql.sock is not produced where basically the mysql database wont work...the system log when it tries mysql is:

linux mysql: No mysql PID file found. Looked for /var/lib/mysql/linux.server.pid

then it stops mysql

---

53 File Not Found: LibmySQL
The server is connected ok but keep getting this error message.

53 file not found: libmySQL.

I have this file in the bin directory: libmySQL.dll.

---

Mysqld Command Not Found
I followed all the instructions in the tutorial section of the manual, but typing mysql -u root I get Error 2002 Cannot connect thru socket. From what I gather, this is because mysqld is not started (since mysql.sock does in fact exist.) Well, if I try to start mysqld, redhat tells me Command not found. So, I thought I'd try mysqld_safe. Well that gave me some feedback:

Starting mysqld daemon with databases from /var/lib/mysql
STOPPING server from pid file /var/run/mysqld/mysqld.pid
050413 [timestamp] mysqld ended

---

Column Data Not Found
i am having this problem where my html form/php handler are not inserting the correct values into the database. I am trying to simply insert a row into a database using the same php code that i always do. could it be the different mysql structure that i am using? when i click the submit button on the page it inserts a row with all 0s. (-|-|0|0|0.00).

sql structure is as followed. i have never set up my own table with integer fields..

CREATE TABLE `mytable` (
`id` bigint(20) NOT NULL auto_increment,
`date` varchar(100) NOT NULL,
`amt_sold` smallint(5) NOT NULL,
`amt_rem` smallint(5) NOT NULL,
`raised` decimal(8,2) NOT NULL,
KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

---

No Tables Found In Database
Installed I have:

PHP Version 4.3.10
MySQL 5.0.2-alpha-Max
phpMyAdmin 2.7.0-pl2

Recently upgraded from MYSQL 4.1.

I can loginto phpMyAdmin fine and my databases are listed on the left hand side, however, clicking on any database shows "No tables found in database".

I guess I need to convert all the tables or something but I'm lost with all the MYSQL documentation!

Even creating a new database via shell and checking it in phpMyAdmin I get the same error?!?

---

Mysqld.sock Not Found
When I enter mysql -u root -p I get this error:PHP Code:

 ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) 

I've searched google and there are hundreds of threads in other forums about this same question. All of them are unanswered that I looked through. I asked in a couple IRC rooms and noone seemed to know exactly what the problem could be. A lot of people were telling me that the socket is probably being created somewhere else but they didn't know how to find out where its being created or how to change it. I checked my.cnf and the sockets are all set to /var/run/mysqld/mysqld.sock.
Does anyone know what the problem is or how to fix it?

---

Handle No Data Found Situation
I am kind of new and am trying to set up error handlers in MySql
5.0.41.
I create a function. Within, I try to keep selecting until I get
nothing back from select.

I cannot seem to catch the error when the select returns nothing.
I declare a handler like this:
DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
SET tester = 01;
more stufff....
END;

DECLARE EXIT HANDLER FOR SQLWARNING
BEGIN
write out to error log...
END;

Then, I have a loop like this:

myloop: LOOP

select x INTO y
FROM z
WHERE a = b;

if tester = -1 then
LEAVE myloop;
end if;

do stuff here....

set b = b + 1;

END LOOP myloop;

The select is guarenteed to retrieve nothing after a few loops around.
but it never ends. The declare continue handler does not fire.

If I run from client command prompt the EXIT handler fires, but I
dont get the warning message, just message thatit exited without a
RETURN.

If I remove all error handlers and run form prompt, it runs endlessly
until I stop it with a counter var. Then I get X number of warnings,
but it doesnt say what the warning is

---

Page Can Not Be Found (time Out And Slow_queries)
it seems that im getting a page can not be found a lot when I try to edit the table information. Ive looked at Runtime Information and Slow_queries 5,220 jump out at me is that a reason for this page error? I have a php page that gets its information from the table.

the information changes regularly and also I have the php page delete entries by date every 8 days. I included the code If theres something I can do to make it not load the server up or what ever. I hope all this makes sence. I guess the ? is why do I get page cant be found, when I try to edit stuff in the table? Code:

---

MAC OS X : 'mysql -u Root' : Command Not Found!
i know nothing about DB's but i want to learn.; after a few attempts i finally got mysql to run (' starting mysql dtabase server') and the next step i am told is to make a pswd. well, i typed 'mysql -u root' to connect to the server like the manual says to do and all i get is 'command not found'.

---

DECLARE EXIT HANDLER FOR NOT FOUND
It is my understanding the following declaration:

DECLARE EXIT HANDLER FOR NOT FOUND BEGIN SET outStatus = statusNotFound(); END;

in the context of this stored procedure:

CREATE PROCEDURE spfSelectById(OUT outStatusSMALLINT UNSIGNED,
IN inSpfIdSMALLINT UNSIGNED)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET outStatus = statusSqlException();
DECLARE EXIT HANDLER FOR NOT FOUND BEGIN SET outStatus = statusNotFound(); END;

SELECT spfId, type, description FROM spf WHERE spfId = inSpfId;

SET outStatus = statusSuccess();
END;;

should trigger the execution of the handler if the select does not find a match on spfId; is my understanding correct or flawed?

---

Innodb_autoinc_lock_mode - Not Found As A System Variable.
I wanted to solve the issue regarding AUTO_INCREMENT resetting itself back to 1 afer a server shutdown. So i did some digging up and read that after mySQL 5.1.22 they started adding a variable to the my.cnf file called

innodb_autoinc_lock_mode

Apparently this variable can store the last value of auto_increment even afer a server shutdown. The problem that im having is that I have mySQL 6.0 (windows). and that variable is not in the my.cnf file. I also checked the manual and this is supposed to be a dynamic variable, meaning i could use SET in the Command line client. I tried this also and it did not work. In fact, when im in the Command Line Client and I enter SHOW VARIABLES;. a list of about 250 variables is displayed but innodb_autoinc_lock_mode is not there.

Im using mySQL 6.0 (for windows) I downloaded the installation program. i did not compile mysql myself.

---

Big SELECT: Ordering Results By Where Matches Are Found
I'm sure there must be an accepted technique for this, but it's something I haven't tried before, so if anyone can point me in the right direction I'd be grateful.

I'm writing a search facility for a site where the data is stored in several tables - let's say 5 for this example - and I want to order my results according to where (if anywhere) matches are found. So...

Let's say I have tables 'speakers', 'topics', 'speakers_topics', 'articles', 'other'.
'speakers' is a table of speakers, with id, name and some text fields.
'topics' is a list of topics they address
'speakers_topics' relates the above two by pairs of id numbers
'articles' and 'other' are further tables of text data with possibly more than one row for some speakers, identified by id.

I want to search the data in the following order:
name from 'speakers'
topics
text data from 'speakers'
text data from 'articles' and 'other'
...and order the results according to where in that hierarchy a match is found.

So, if the user's search term matches one speaker's name field, another's topic and someone else's text data, that's the order in which the results should be ordered. Also, if the same person is matched from, say, both name and text fields (which is very likely, as their name will almost certainly appear in some of the text), the name should take precedence in the ordering.

To complicate matters further, I'd like if possible to extend this to an and/or situation. If the user enters two or more words, any results that match all the words should be ordered above those that match only some of the words.

I can probably do this relatively easily with a series of separate queries (I'm doing all this from PHP, by the way), but that strikes me as inefficient.

---

How To Find Duplicates In Two Columns And Count For Each Row Found
I have a little advanced task at hand. I want to search in a table, and find duplicates and count each duplicate found.

Imagine this table

Table | Column Name

Userinfo
Firstname | Lastname

In this table there are many with the same firstname and lastname. I would like an sql statement which find these duplicates and for each duplicate also writes how many occurences it found. So the output I would like is something like this:

John Smith 5
Eva Something 8
and so on.

---

Deadlock Found When Trying To Get Lock, Try Restarting Transaction.
Im getting the following SQLException during an insert or a delete or an update statement. "Deadlock found when trying to get lock; try restarting transaction message from server: lock wait timeout exceeded; try restarting transaction".

I have a jsp page which does an insert, i have another jsp page which has a delete query that deletes a particular row, and then tries to insert (using an id,which is a primary key), then there is an update statement. In both these pages, i sometimes get the above mentioned exception. When the Tomcat server is restarted, i do not get the exception.

---

Joining Two Tables And Getting Only Those Results, Where No Matching Entires Have Been Found
I have two tables, "fantasybets" and "fantasybets_placed".

What I want to achieve, is to query "fantasybets" and "fantasybets_placed" to only display those bets, where a given user has paced no bets yet.

Here are examples for my tables:

BETS
______
bet_id
bet_name

BETS_PLACED
______
bet_placed_id
bet_placed_username

SELECT * FROM bets
LEFT JOIN bets_placed
ON bets.bet_id=bets_placed.bet_placed_id
WHERE ((bets_placed.bet_placed_id IS NULL)
OR (bet_placed_username <> "given_username"))

However, this doesn't work as expected, as i get all those bets for a given user, where some other user has placed bets on too. However, I only want those bets, where a defined user "given_username" has NOT placed a bet yet.

---

MySQLerror: Data Source Name Not Found And No Default Driver Specified
using this connection string;

"DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;PORT=3306;DATABASE=dbtest; USER=root;PASSWORD=admin;OPTION=3;"

i still encounter this message:

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

my connection works on the server.. but it doesnt work in clients?

---

Adding Row Number In SQL Select (Found Solution, Nevermind)
I need to get back ranked results from a simple select query. For instance, if I said: select rowNum, FNAme, LName from table, I would be looking for results like this:

1 | John | Smith
2 | Jame | Doe
3 | Another | Person
.....
54 | Last | Guy

EDIT: .

---

No Mysql Pid File Found. Looked For /var/lib/mysql/localhost.localdomain.pid
The log of my system says:

"no mysql pid file found. looked for /var/lib/mysql/localhost.localdomain.pid"

how to deal with problem?

---

Returning "binary Flags" With Found Record(s)
I am using the 'binary flag' principle for defining (multiple) modes for an
element..

Elements table:
id name flags
1 a 9
2 b 3
3 c 11

Flags table:
id value name
1 1 mode 1
2 2 mode 2
3 4 mode 3
4 8 mode 4

Now..
Is it possible with one query to get all the modes listed??
to get a result like:
1 a mode1, mode 4
2 b mode 1, mode 2
3 c mode3, mode 4

---

Query Table A And Return Records Not Found In Table B.
Given table A and table B.  Table A is a master table and table B is a list of authority or reference which is fed by selecting records from table A (via an UI I wrote in PHP).

The interface is working fine but I am left with one problem.    If I need to edit my table of authority, the source table (table A) should return ONLY entries not previously selected.

That said, if the A.key is in B.key, records from table A matching B.key should be omitted.

I could do this by looping through the query results from table A and querying table B, if no match, show A.key.  This I think is a bit antiquated and possibly too involved.

Is there a joint query command I can use?

---

Log-error File 'not Found' But This Same File Being Written To?!
I recently put up a message board and I get around 150+ hits a day so far. I've recently had some people sign up with my board but unfortunately, no one seems to be posting (they're just lurking around). I'm currently doing some promotions in the hope that others will post (like giving away a Lord of the Rings poster to all users who has 20 posts or more).

Of course, my board has only been up for less than a month (3 weeks to be exact) so I know I need to be patient but I would appreciate any advice you can give me on how to turn lurkers into posters.

---

PHP Page Displaying When First Hit, Page Not Found On Refresh.
I have installed MYSQL sucessfully and PHP. I usually code in ASP (I know, I know) & SQL Server (Yes OK). And have recently turned to the light!

When I load a page for the first time (On IIS) it looks fine and new data change in the database is displayed and it looks great.

When I click a link to another page with links to the DB then I get Page Cannot be found, if I go back to the URL for the site (thus index.php) Refresh and then click the link it works??!

---

Complex Query - UPDATE Within UPDATE?
Edit: Before anyone leaves this thread, don't be put off by the regular expressions! They are not the problem, so please stay and read.

OK, this query has got my head spinning. I am basically creating a query that goes through each product in a table to update the stock for that particular item with that particular size (i.e. I am talking about shoes - different models and each model has different sizes (uk kids 12 -> uk 11).

With each shoe it does (or is meant to do) the following:
1. The PHP script that runs the query is looping through every size outside of the query
2. So for each of these sizes it checks to see whether the product it is currently on matches the size it is on
3. When it finds the size it is on, it then deducts the correct number of units from the stock table
4. The final WHERE clause makes sure this subquery inside the UPDATE only happens when the StockUpdated field of the Product table equals 0 (in other words, the stock hasn't been counted before)

Basically what I need to do, is first to make sure what I currently have got does the above correctly but also I need the query to UPDATE the StockUpdated field to 1 only when it has been updated successfully. How could I do this? Unfortunately I cannot just add an extra update entry to the end of the query as this would update the StockUpdated field regardless of whether it has been properly counted or not.

Here is the query I have so far (with a little simple PHP around it doing the loop):


PHP

$shoesizes = array(1 => 'ukk12','ukk13','uk1','uk2','uk3','uk4','uk5','uk6','uk7','uk8','uk9','uk10','uk11');
    $numshoesizes = count($shoesizes);
    
    for($i = 1; $i < $numshoesizes; $i++) {
        $stockupdate = "
        UPDATE heelys_stock,items_ordered SET heelys_stock.size_".$shoesizes[$i]." =
            (SELECT
                CASE
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?( )?(Kids)?( )?( )?(UK)?( )?( )?(Kids)?( )?( )?[^0-9]12( )?(' -- if UK Kids 12
                THEN heelys_stock.size_ukk12 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?( )?(Kids)?( )?( )?(UK)?( )?( )?(Kids)?( )?( )?[^0-9]13( )?(' -- if UK Kids 13
                THEN heelys_stock.size_ukk13 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]1( )?(' -- if UK 1
                THEN heelys_stock.size_uk1 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]2( )?(' -- if UK 2
                THEN heelys_stock.size_uk2 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]3( )?(' -- if UK 3
                THEN heelys_stock.size_uk3 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]4( )?(' -- if UK 4
                THEN heelys_stock.size_uk4 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]5( )?(' -- if UK 5
                THEN heelys_stock.size_uk5 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]6( )?(' -- if UK 6
                THEN heelys_stock.size_uk6 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]7( )?(' -- if UK 7
                THEN heelys_stock.size_uk7 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]8( )?(' -- if UK 8
                THEN heelys_stock.size_uk8 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]9( )?(' -- if UK 9
                THEN heelys_stock.size_uk9 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]10( )?(' -- if UK 10
                THEN heelys_stock.size_uk10 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]11( )?(' -- if UK 11
                THEN heelys_stock.size_uk11 - (items_ordered.Amount/items_ordered.Price)
            FROM items_ordered WHERE items_ordered.StockUpdated = 0)
                
            WHERE (heelys_stock.id = (SELECT heelys_stock.id FROM heelys_stock,heelys_shoe WHERE SUBSTRING_INDEX(items_ordered.Product,',',1) = heelys_shoe.full_shoe_name))
        , items_ordered.StockUpdated = 1" // at the moment this last update of the items_ordered table happens to every record!!! even if the other part of query fails

        
        // update stock for size $i
        mysql_query($stockupdate);
    }

Hope someone can see how I can do this? I've been working on this query for 2 or 3 hours now and I've been making reasonable progress but now I am really stumped.

---

Update Replication, Force Update
Recently an error in the db on my master caused the slave to fail. I noticed this after a few days when I looked at the status and it said "Slave_SQL_Running: No". After looking further I saw what the error was on the master.

What is the best method of re-synching the databases?

Is there a command to force a re-replication or synch of the dbs? Would you delete the slave's db and update over? In this case, is there a command to pull the data down from the master?

---

Unable To Update - Have Update Priveledge
I've been granded update priveledge to tables in a few databases on our server:

Select_priv ,Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, References_priv, Index_priv, Alter_priv, Create_tmp_table_priv, and Lock_tables_priv are all set to 'Y' for a selected database.

I can connnect and read the data and data definitions just fine.  I cannot insert, update, delete, create, or alter any table in this database.

Our admin worked for a couple of hours yesterday trying to resolve the issue, but no joy.  I've googled for this problem and the most relavent posts I've found were problems related with users not being able to connect. I can connect and I can read, I just can't do any of the other priviledges.

---

Duplicate Key
I'm trying to create a new table that will store a name in various languages. When I run the following, I get a "Duplicate entry '1' for key 1 " error.

I understand that it is because the language ID is the same for the first two inserts, but I don't see how to get around that. Can someone please tell me if the table is setup incorrectly or explain what I need to do to get this to work?

DROP TABLE IF EXISTS header;
CREATE TABLE header (
page_name varchar(64) NULL,
language_id int DEFAULT '1' NOT NULL,
PRIMARY KEY (language_id)
) TYPE=MyISAM;

INSERT INTO `header` ( `page_name` , `language_id` ) VALUES ('index', '1');
INSERT INTO `header` ( `page_name` , `language_id` ) VALUES ('product', '1');
INSERT INTO `header` ( `page_name` , `language_id` ) VALUES ('product', '2');

---

ON DUPLICATE KEY
i have finally got on query working, but it does with "40 rows affected" !! There are only 17 records in my totals table, and in this case only one record gets updated. So two queries would have:

"INSERT IGNORE..." - only 17 to compare.
"UPDATE WHERE..." - only 1 record updated.

INSERT INTO v8totals (tid,tyy,tmm,prices)
SELECT owners,2007,05,SUM(finalprice) FROM v8x200705 GROUP BY owners
ON DUPLICATE KEY UPDATE prices=(SELECT SUM(finalprice) FROM v8x200705 WHERE tid=owners GROUP BY owners);

So either i have written my query badly (and i tried many ways) or this ON DUP UPDATE is very inefficient.

---