Hi all!

I need some with ORDER BY in the following example. I want to order by
selected category, then by subcategories of the selected category, then by
categories with the same parent_id of the selected category, then by random
if possible, or random within the categories if possible, but the first
order by part is not working because is not returning products of the
selected category first, instead returns products from a top category
(parent_id = 0).

table categories
id | parent_id | category
- where parent_id is 0 for top categories.

table products
id | id_category | product

SELECT
products.id,
(SELECT
CASE WHEN CHAR_LENGTH(products.product) > 40 THEN
CONCAT(SUBSTRING(products.product,1,37),'...')
ELSE products.product END) AS product,
(SELECT
CASE WHEN CHAR_LENGTH(products.description) > 70 THEN
CONCAT(SUBSTRING(products.description,1,67),'...')
ELSE products.description END) AS description
FROM
products
WHERE
products.id_stock = 1
ORDER BY
products.id_category IN (".$id_selected_category."
, (SELECT categories.id
FROM categories
WHERE categories.parent_id = ".$id_selected_category.")
, (SELECT categories.id
FROM categories
WHERE categories.parent_id = ".$parent_id."))
, RAND() LIMIT 11

(the php vars have correct values)

Please apologise my bad English.
Thanks in advance.
Pedro.

Hi ,

I think i might have hit a bug, posted on forums.mysql.com but
apparently nobody really reads that i think.

my table:

CREATE TABLE `clog` ( `cID` int(20) NOT NULL auto_increment, `lID`
int(10) default NULL, `ip` int(10) default NULL, `timestamp` int(11) NOT
NULL, PRIMARY KEY (`clickID`) ) ENGINE=MYISAM; or i use ARCHIVE

I have a bit of a problem that occurs only when i change my really
simple log table to the archive engine. The replication breaks. Any
thoughts? The row number of the error is variable. When the table is set
to myisam, the replication does not break on duplicate key, and runs as
expected.

Can't write; duplicate key in table 'clog'' on query.

Is it possible that due to the stress of the benchmark, my slave cannot
compute the next cID or creates a duplicate (cId is the only variable
that changes, on bench query)?

W00ps forgot to update subject of my email,

WiNK / Rob wrote:
> Hi ,
>
> I think i might have hit a bug, posted on forums.mysql.com but
> apparently nobody really reads that i think.
>
> my table:
>
> CREATE TABLE `clog` ( `cID` int(20) NOT NULL auto_increment, `lID`
> int(10) default NULL, `ip` int(10) default NULL, `timestamp` int(11)
> NOT NULL, PRIMARY KEY (`clickID`) ) ENGINE=MYISAM; or i use ARCHIVE
>
> I have a bit of a problem that occurs only when i change my really
> simple log table to the archive engine. The replication breaks. Any
> thoughts? The row number of the error is variable. When the table is
> set to myisam, the replication does not break on duplicate key, and
> runs as expected.
>
> Can't write; duplicate key in table 'clog'' on query.
>
> Is it possible that due to the stress of the benchmark, my slave
> cannot compute the next cID or creates a duplicate (cId is the only
> variable that changes, on bench query)?
>
>
>

Thing 1: your auto_increment key MUST be your primary key.

Thing 2: the timestamp field will be updated with the current epochal
timestamp which only increments every second.. as you have a
timestamp field as you primary (and therefore unique) key, you will
never be able to perform more than one INSERT/UPDATE within the span
of any given second.

you need to redign the table, I'm afraid.

On 9/11/07, WiNK / Rob <wink@connectedserver.com> wrote:
> Hi ,
>
> I think i might have hit a bug, posted on forums.mysql.com but
> apparently nobody really reads that i think.
>
> my table:
>
> CREATE TABLE `clog` ( `cID` int(20) NOT NULL auto_increment, `lID`
> int(10) default NULL, `ip` int(10) default NULL, `timestamp` int(11) NOT
> NULL, PRIMARY KEY (`clickID`) ) ENGINE=MYISAM; or i use ARCHIVE
>
> I have a bit of a problem that occurs only when i change my really
> simple log table to the archive engine. The replication breaks. Any
> thoughts? The row number of the error is variable. When the table is set
> to myisam, the replication does not break on duplicate key, and runs as
> expected.
>
> Can't write; duplicate key in table 'clog'' on query.
>
> Is it possible that due to the stress of the benchmark, my slave cannot
> compute the next cID or creates a duplicate (cId is the only variable
> that changes, on bench query)?
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=mdykman@gmail.com
>
>


--
- michael dykman
- mdykman@gmail.com

- All models are wrong. Some models are useful.

also when it hits a dupl. and i skip the record, and continue slave it
hits another duplicate entry almost instantly, when i then drop the
table on the slave, and recreate it manually and set it to myisam at
that time, the slave continues without a problem.


On Tue, 2007-09-11 at 16:42 +0200, WiNK / Rob wrote:
> W00ps forgot to update subject of my email,
>
> WiNK / Rob wrote:
> > Hi ,
> >
> > I think i might have hit a bug, posted on forums.mysql.com but
> > apparently nobody really reads that i think.
> >
> > my table:
> >
> > CREATE TABLE `clog` ( `cID` int(20) NOT NULL auto_increment, `lID`
> > int(10) default NULL, `ip` int(10) default NULL, `timestamp` int(11)
> > NOT NULL, PRIMARY KEY (`clickID`) ) ENGINE=MYISAM; or i use ARCHIVE
> >
> > I have a bit of a problem that occurs only when i change my really
> > simple log table to the archive engine. The replication breaks. Any
> > thoughts? The row number of the error is variable. When the table is
> > set to myisam, the replication does not break on duplicate key, and
> > runs as expected.
> >
> > Can't write; duplicate key in table 'clog'' on query.
> >
> > Is it possible that due to the stress of the benchmark, my slave
> > cannot compute the next cID or creates a duplicate (cId is the only
> > variable that changes, on bench query)?
> >
> >
> >
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=m...ctedserver.com
>
>


--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.