mysql-query

mysql alphabetical order

hi i am trying to sort mysql data with alphabeticaly like A | B | C | D when i click on B this query runs select name from user order by 'b' but result showing all records starting with a or c or d i want to show records only starting with b thanks for help ...

extract only text or image from blob (php mysql)

Hi, I have BLOB field in MySql table with text and image inside, is there any way to extract out only text or only images? $query = "SELECT id, date, blob, FROM table ORDER BY id DESC "; $result = mysql_query($query); while($row = mysql_fetch_assoc($result)) { echo "<div class=all> "<ul class=id>{$row['id...

Zend DB: joinLeft problem

I am trying to construct this query with Zend DB: SELECT `s` . * , `a` . * FROM `shr_statement` AS `s` LEFT JOIN `shr_answer` AS a` ON a.statement_id = s.statement_id AND a.user_id =1 WHERE (s.language = 'en_US') AND (s.is_active =1 ) ORDER BY `order` ASC I read about a possible issue with t...

MySql syntax question

Why does this work perfectly: SET @columnQuery = 'INSERT INTO ColumnNames SELECT ORDINAL_POSITION, COLUMN_NAME FROM information_schema.columns WHERE TABLE_NAME=''residents'' ORDER BY ORDINAL_POSITION;'; PREPARE STMT FROM @columnQuery; EXECUTE STMT; but this does not: DECLARE TableName VARCHAR(50); SET @TableName = 'residents'; SET ...

MySql syntax question regarding CONCAT and strings

Environment is MySql 5.1.5. This is a snippet from a larger stored procedure. Assume the variables are properly declared and set before this code is reached. When I run the following loop, something seems to be failing in the CONCAT. @columnName and @xmlQuery are both VARCHARs. When I "SELECT @xmlQuery" at the end of the procedure, ...

Code sample to remove all special characters from data stored in MYSQL ?

Does anyone have a code sample where I can to remove all special characters from data stored in MYSQL? I need to remove the following special characters: : ~!@#$%*()_+{}[];':"<>? ...

How to check that a value is unique before importing a row into mySQL?

I have a database table that I need to import a bunch of records into. I don't want to overwrite existing records if they're already in the database, so I've set things up to do a select query first to check if there's a value, but apparently the rows are importing too quickly for the index to keep up, as I'm getting duplicates created f...

MYSQL - Concatenate two tables

I have two tables as follows: TABLE A TABLE B StuID | actid FacID | actid 3 12 98 17 5 17 54 21 I want to list the name of everyone, both students and faculty, who participate in activity 17. Is there anyway I can get a result as below: ...

Need a query to move all the emails from one table into another using a common row value as the link

An import was performed on my database which wasn't done so well. It's resulted in some fields being empty where they shoudln't, etc... What I need to do now is move all the data from one column in one table into a column in a different table. Both tables have an ID which is a link to each rows. For example: Table1 id | linkID | emai...

mysql query for alphabetical search

Hi , How to retrieve the records those character star starting with A OR B OR C , I just tried this query SELECT * FROM `test_tbl` WHERE Left(cus_name, 1) = 'a' ---> display all customer name starting with character a AND i added one more condition, That is SELECT * FROM `test_tbl` WHERE Left(cus_name, 1) = 'a' or Left(cus_name...

MySQL query help

Hi All, Need a help in building query. I have quantity_price table, listing quantity and corresponding price as shown below Quantity Price ---------------- 1 -- € 175,35 2.5 -- € 160,65 5 -- € 149,10 10 -- € 143,85 so for upto 1 quantity price will be 175,35 up to 2.5 it will be 160,65 and so on. For more than 10 quantity...

writing a sql query in MySQL with subquery on the same table

I have a table svn1: id | date | startdate 23 2002-12-04 2000-11-11 23 2004-08-19 2005-09-10 23 2002-09-09 2004-08-23 select id,startdate from svn1 where startdate>=(select max(date) from svn1 where id=svn1.id); Now the problem is how do I let know the subquery to match id with the id in the outer query. Obviously id=svn...

Storing comments with MySQL execution

Hi guys! I have searched for the answer to this one and haven't found it. I think it's simple. We have several users on our system that are (via programmatic methodology) using the same user id to run MySQL commands. I have been tasked with storing the user environment variable when these commands are ran. I guess my question is this: c...

How to generate these two reports with MySQL?

My Schema I have the following tables table notes/example values ------------------------------------------------ users ( id email # "[email protected]" ) games ( id name # "Space Invaders", "Asteroids", "Centipede" ) players ( ...

mysql : a pivot table handler request ?

Hi all, I have a mysql question : using php with mysql I actually have a schema that looks like this : 3 tables : module module_has_theme (pivot table) theme The idea behind this is to display the modules of my application using themes if they have, and if they don't, then display them in an extra category (other). So let's say if...

how do I capture and return a scalar result from a my-sql prepared statement?

So I wrote this nice little stored procedure that accepts 3 parameters, assembles a query string, executes a query, and retrieves a single row of data from the specified table. It then converts the single row into well-formed XML. The call to EXECUTE query2; is where the XML is returned. When running it in the Toad MySQL Editor, obvio...

Escaping Characters from MySQL from PHP Framework

I was wondering if when using the database library in Codeigniter there was a way to automatically escape all the inputs to prevent injection. I know I can use mysql_real_escape_string() to do it, but I wondered it this was already setup to do this automatically, if not are there any frameworks that have this included? Thanks! ...

SQL List Game Database By Most Likes

So my site's main page display's applications for a PMP. Users can rate these apps with 'Like', 'Dislike', or 'Neutral/None' (every app is rated Neutral/None by default.) On the main page I wanted to list the games by the highest difference (So it would take the total Like's and Dislike's then add them together.) I'm having difficulty m...

How can I order a query result same as the id specified in the WHERE condition?

I have a query like this SELECT * FROM test JOIN test2 ON test.id=test2.id WHERE test.id IN (562,553,572) GROUP BY test.id Its results are ordered like this: 553, 562, 572... But I need the same order that I specified in the IN(562,553,572) condition. How can I do that? ...

mysql join question, joining newest record in a one to many relationship.

Hi all, I have a simple (hopefully) SQL question for you, any help would be much, much appreciated :). I have two tables which I would like to join. One Is Users, lets say it's called users One is a kind of history of that user, lets say its called users_history. The relationship of these two is a one users to many users_history rel...