mysql

MS Office no longer works as BLOB

Hi does anyone know why MS Office such as doc, docx and xls can no longer be viewed when retrieved from a mysql db when stored as Blob? The doc and docx used to download and open without any problem, but now it no longer recognises the file format. ...

Selecting the latest per group of items

i have 2 tables products and cost PRODUCT ProdCode - PK ProdName COST Effectivedate - PK RetailCOst Prodcode i tried this query: SELECT a.ProdCOde AS id, MAX(EffectiveDate) AS edate, RetailCOst AS retail FROM cost a INNER JOIN product b USING (ProdCode) WHERE EffectiveDate <= '2009-10-01' GROUP BY a.ProdCode; uhm yah its show...

mysql exit handler help

I have a simple update that will use a transaction. If the transaction fails, I want to rollback and send out an integer value of 0. Here is my code so far. It isn't working and all I get back is integer 1 even when it fails. declare exit handler for not found rollback; declare exit handler for sqlwarning rollback; declare exit handler ...

Storing messages as BLOB (Binary Large Object) or ordinary text?

What is the best way to store messaging texts in large/big web sites: BLOB or ordinary text in table or something else? Database: MySQL. ...

Filtering duplicities by another table.

Hello, I have two (example) tables here A) data_table: +----------+-------+ | location | value | +----------+-------+ | 43 | 38 | | 44 | 31 | | 3 | 31 | | 11 | 38 | | 47 | 35 | | 49 | 31 | | 50 | 31 | | 55 | 16 | | 56 | 16 | | 59 | 35 | ...

Why is this SQL query not working?

this script have to update things on every refresh but not working. lend me a hand $yp = mysql_query("select id from yyy where twitterid = '$tid'"); $qq = "update yyy set twitterid = '$tid', twitterkullanici = '$twk', tweetsayisi = '$tws', takipettigi = '$tkpettigi', takipeden = '$tkpeden', nerden = '$nerden', bio = '" . mysql_re...

Connect multiple JCR repositories to one database (and keep updated)

I am a little bit stuck in the moment because I don't know if my problem is solvable at all. I have one database (MySQL) where a JCR repo is stored. Secondly do I have two webapps (edit/live) that share this data. Live is designed to read only, so it can (really??) be considered save regarding concurrency. My problem is that everythin...

Getting parental status from a self-referencing table

Let's say you have the following table: items(item_id, item_parent) ... and it is a self-referencing table as item_parent refers to item_id. What MySQL supported SQL query would you use to SELECT each item in the table along with a boolean value that indicates whether that item is a parent / has other items referencing to it? If you ha...

MySQL Select Like

I've got a field in my table for tags on some content, separated by spaces, and at the moment I'm using: SELECT * FROM content WHERE tags LIKE '%$selectedtag%'" But if the selected tag is 'elephant', it will select content tagged with 'bigelephant' and 'elephantblah' etc... How do I get it to just select what I want precisely? Thanks...

Most efficient way to find index'd records in mysql?

hey all I have tables with millions of rows in them and some of the select queries key off of 3 fields company, user, articleid would it be faster to create a composite index of those three fields as a key or MD5 (company, user, articleid) together and then index the hash that's created. ? thanks ...

CONCAT for all results, or some kind of wrapper function for MySQL

This may be really easy but I'm wondering if there is a function in MySQL to have all results wrapped or appended without having to use CONCAT on each part? Something like: SELECT x, y, z WHERE 1 = 1 Where the results would normally be: x | y | z blah |stuff |junk big |small |huge good |bad |ugly Instead it returns...

Character set in MySQL; Can't insert special characters, why?

I can't insert special characters. Whenever I try to, all characters BEFORE the special character gets inserted, but after the special character nothing is inserted into the field. $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die('Error connecting to mysql'); mysql_select_db($dbname,$conn); mysql_set_charset('utf-8', $conn); mys...

Edited- MySQL. Large MyISAM table (40mln records) having index that is very slow and huge in size on disk

The table contains about 40,000,000 records having: CREATE TABLE `event` ( `id` bigint(20) unsigned NOT NULL auto_increment, `some_other_id_not_fk` int(10) unsigned default NOT NULL, `event_time` datetime NOT NULL, `radius` float default NULL, `how_heavy` smallint(6) default NULL, PRIMARY KEY (`id`), KEY `event_some_other...

How do I make a SQL table from the result of another select?

I'm asking for MySQL specifically, but the answer might work for a lot of SQL databases. If I have a select statement like this: select * from users where age > 5; How do I assign that to a new table? ...

Another mysql JOIN question...

I have these tables: classified: classified_id (PK) price headline cat_id // THIS IS ANYTHING FROM 1 TO 30 DEPENDING ON CATEGORY. IT IS SO THAT I CAN LINK WHICH CATEGORY TO USE IN THE CATEGORY TABLE BELOW text etc... category: cat_id (PK) cat_name category_options: option_id (PK) cat_id (FK) // FOREIGN KEY FROM CATEGORY TABLE... optio...

mysql @@identity vs sql-server last_insert_id()

Am I correct in understanding that mysql's LAST_INSERT_ID() function doesn't reset between insert attempts (as @@identity does in sql-server)... that if the immediately preceding insert operation fails, LAST_INSERT_ID() will return the pk of whatever the pk of that connection's last insert to any table with an auto-incrementing primary k...

MySQL headache, should I or should I not?

I have a classifieds website. I am using SOLR for indexing and storing data. Then I also have a MySQL db with some more information about the classified which I dont store or index. Now, I have a pretty normalized db with 4 tables. Whenever ads are searched on the website, SOLR does the searching and returns an array of ID_numbers whic...

My MySQL query won't work

I have the following query which searched an episode guide database for user inputted data: $query = "SELECT * FROM epguide WHERE EpisodeTitle LIKE '%$trimmed%' OR Synopsis LIKE '%$trimmed%' OR Notes LIKE '%$trimmed%' ORDER BY o"; This works OK, but when I add 'Series = '...

PHP: letting your own function work with a while loop

Hi i have this code $qVraagGroepenOp = "SELECT * FROM $tabele WHERE $where"; $rVraagGroepenOp = mysql_query ( $qVraagGroepenOp ); $aVraagGroepenOp = mysql_fetch_assoc ( $rVraagGroepenOp ) and i converted that to a fucntion vraagOp("testtable","testtable_ID = $id"); function vraagOp($table,$where) { $qVraagOp = "SELECT * FROM $ta...

Is this how a modern news site would handle it's sql/business logic?

Basically, the below image represents the components on the homepage of a site I'm working on, which will have news components all over the place. The sql snippets envision how I think they should work, I would really appreciate some business logic advice from people who've worked with news sites before though. Here's how I envision it: ...