MYSQL :: Use Where In Query So It Returns Rows That Has Less Than 10 Characters In The Word

Query Returns Empty Set
i have this query:
Code:

select p.url,s.tipo,pw.url as wrapper from secciones s
join plantillas p on s.plantilla=p.clave
join plantillas pw on p.wrapper=pw.clave
where s.clave=1

it works fine, the problem is that from time to time the second join condition won't match, because by design the field p.wrapper is optional, if that happens the query returns 'empty set' even if the where condition and the first join condition do match, is it possible to modify that query so it needs to match only the first join and the where clause to return the result set (i.e. make the second join optional)?

Query Two Tables For A Word
I have a table of words (dict), a basic dictionary. And i have a spell check page that will check if words are in the dictionary using this query:

select * from dict where words = '$Words[$W]'

I want to add a secondary table (dictlearn) with new words that the use can add. When I try this I keep getting an ambiguous error for words:

select * from dict, dictlearn where words = '$Words[$W]'

How can I query both tables for a word to see if there are any matches in either table?

Query Returns Unexpected Users. Not Using Join.
Having a bit of a problem trying to get the correct results. Basically I have three tables I need to select from; user, platform and game.

I'm running the following query

select distinct
u.user_name,
u.user_avatar,
u.user_rating
from
user_game ug,
platform p,
user u
where
ug.game_id = 1
and p.platform_id = 1
and u.user_country = 1
Basically I want it to return users who own the game "1", have it on platform "1" and live in country "1".

Unfortunately does not return users who meet ALL WHERE criteria.

Any ideas where I am going wrong? I know an alternative is to use JOINS but I don't want the performance to be hit by doing that.

My Query Returns Mysql Error 1267....why?
my query is not working... i am using 2 databases...

Running A 'Distinct' Query Returns Some Results TWICE !?
I've spent hours trying to find out why a perfectly simple query that uses the 'distinct' keyword such as:

Select distinct(field1)
From tablename;

works fine based on eg. field1, but when running it on eg. field2 in the very same table, it returns 2 results (yet, only on some words!). Pls see example of result below:

Eg of a 'faulty' result: .....

Cleaning Rows In The Database From Strange Characters...
I have emails in my customers table. At the end of some of the email addresses I have a strange character:?

email@email.com plus two square looking characters

How can I trim my emails so that they are cleaner?

Limit Number Of Rows Or Characters Returned In A Text Field
I understand you can limit the rows of a recordset, but can you somehow tell a query to limit the rows (or characters) returned within just a text field?

I have a page that returns abstracts of many articles and I want to truncate the description for each to just 200 characters. Is there a way to accomplish this with MySQL?

Key(e) Which Is In Middle Position Of A Word And In Last Of The Word
code

select id,myString
from myTable
where
myString like '%e%'

result

(1) I love you
(2) Here we go
(3) Nice to meet you
(4) mell me.
I have myTable like the above.

I like to produce only the record which has the key(e) is in middle position of a word and in the last position of the word.

The following trial code doesn't work correctly, but it will show what I want.

trial code

select id,myString
from myTable
where
myString like '% %e%e %'

result

NONE

target result

(2) Here we go.

Zero Rows Or One Rows Returned, Same Data And Same Query
I have a query that produces a single row (as I expect) when I run it from the mysql client (mysql 4.0.18-Max/linux, also 5.0.19-standard/OSX-intel), or from sqlgrinder (osx, uses jdbc).

When I run it inside my application (a Java app connecting via jdbc), I get zero rows from this query.

I tried it under phpmyadmin, and once again I get zero rows.

Why do I get inconsistent results? Here's the query:

Need Query That Will Return Firstnames With X Characters
I have spammers who post on my website. They like to use things like Home-loan-refinancing as their first name when registering. So I need a query that will return all rows where the firstname field contains 20+ characters or something like that.

INSERT Query Ignoring Escape Characters
I have extensively searched the web, and none of the solutions I've found seem to work (mysql_real_escape_string, addslashes, str_replace). Php will display the reformatted strings properly, but when I attempt to add a reformatted string to an INSERT or UPDATE query, the escape characters seem to be ignored.

On INSERT, fields that contain the " ' " single quote (like Joe's) are losing all data after the single quote. The query DOES insert the record, and the rest of the record is inserted properly, but the field containing the single quote is truncated. "Joe's Crabshack" gets stored as "Joe" ....

Several Rows From One Query
I've this sql-query...

$sql = "INSERT INTO database (some_kind_of_id, names) VALUES $xxx, $_POST['xxx2']";

The thing is that my post xxx2 are several values that I want inserted on several rows with the values xxx in front...

Last N Rows Of The Query
SELECT expensive query ORDER BY field ASC;
It generates somewhere around 2.9m rows. I want the last 10:

SELECT expensive query ORDER BY field DESC LIMIT 10

But I want them the other way around. Sure, I can do that programatically, but for "application reasons" I want that done in the query, so what I want to do is along the grounds of

SELECT expensive query ORDER BY field LIMIT 10 OFFSET rows()-10
We're using MySQL 4.0. Is there a way to achieve the above without using a temporary table?

SQL Query Not Outputing All Rows.
My query doesn't seem to output all 6 rows in the database only two queries and i'm unsure why. I do know it is the query as when i do
SELECT * FROM case_studies
It outputs all rows.
The query i am usign is:
select programs.program_title,
programs.id, case_studies.id,
case_studies.title, case_studies.author,
case_studies.timestamp from case_studies,
programs WHERE programs.id
= case_studies.id
Any ideas?

Query Help, Comparing Rows
Suppose I have the following data:

+----+----------+-----+-----+-----+-----+-----+-----+-----+
| Id | Time | Sun | Mon | Tue | Wed | Thu | Fri | Sat |
+----+----------+-----+-----+-----+-----+-----+-----+-----+
| 11 | 11:20:00 | F | T | T | T | F | F | F |
| 12 | 11:45:00 | F | T | T | T | F | F | F |
| 14 | 12:10:00 | F | T | T | T | F | F | F |
| 15 | 12:35:00 | F | T | T | T | T | T | F |
| 17 | 13:00:00 | F | T | T | T | T | T | T |
| 18 | 13:25:00 | F | T | T | T | T | T | T |
| 19 | 13:50:00 | F | T | T | T | T | T | T |
| 20 | 11:28:00 | F | T | T | T | T | T | F |
| 21 | 11:53:00 | F | T | T | T | T | T | F |
| 22 | 12:18:00 | F | T | T | T | T | T | F |
+----+----------+-----+-----+-----+-----+-----+-----+-----+
I would like to output the data by day pattern. I need some way to determine that in the above table, Mon-Wed is the same, Thu-Fri is the same and Saturday and Sunday are unique.

How Many Rows Were Affected By Query?
How do I retrieve the number of affected rows by an UPDATE query with SQL? The C API exposes mysql_affected_rows() but I can't find documentation of the SQL equivalent...

I'm trying to find if an UPDATE had an effect within a stored procedure, so I can do an INSERT if there's no row to update.

Doing it the other direction to generate an error to act on would be quite wasteful... one INSERT and millions of errors per day.

Eliminate Rows In A Query (without Many OR)
This is my query :

SELECT cas.id,cas.noCas,cas.nomFictif,cas.prenom,cas.naissance FROM cas WHERE LEFT(noCas, 3)<'300' AND cas.id<>53 AND cas.id<>61 AND cas.id<>173 AND cas.id<>174 AND cas.id<>178 AND cas.id<>185 AND cas.id<>598 ORDER BY noCas

There must be a more efficeint way to find what i want than this :

AND cas.id<>53 AND cas.id<>61 AND cas.id<>173 AND cas.id<>174 AND cas.id<>178 AND cas.id<>185 AND cas.id<>598

Something like cas.id is not (list of values)...

Query With 3.3million Rows Is Slow?
I'm not that great with MySQL...so I was hoping someone could help me out.
The query I'm running is too slow...can anyone tell me what I can do to
speed it up..if I can at all? I was wondering if because ZipListMatrix has
3.3 million rows that 8 seconds is all the faster it's going to be. Any
help is greatly appreciated! I have already "optimized" the tables.

Updating Multiple Rows In One Query
tried to find the answer with search but didn't return any answers.

OK, here is the table

table test
------------------------
| test_id | test_order |
------------------------
| 1 | 1 |
------------------------
| 2 | 2 |
------------------------
| 3 | 3 |
------------------------
I'm trying to change the orders in one query, but not sure how to do that.

phpMyAdmin shows me the code like this

Quote:

$sql =
'UPDATE `test` SET `test_order` = ƈ' WHERE `test_id` = 1;'
'UPDATE `test` SET `test_order` = Ɖ' WHERE `test_id` = 2;'
'UPDATE `test` SET `test_order` = Ƈ' WHERE `test_id` = 3;'
. ' '

I'v tried that but got a syntax error.

MySQL version is 4.0.26, can anyone help please?

How To Update Multiple Rows With One Query?
I am using PHP/MySQL and need to update 7 rows with one query. Can someone tell me how to do the following so it will update the row for each day of the week? (This obviously doesn't work)

$sql = "UPDATE business_hours SET hours='$sunday' WHERE id='$id' AND day='sunday' AND SET hours='$monday' WHERE id='$id' AND day='monday'";

Query Pulls Out Multiple Rows Even Though Theres Only One
Ive got a query thats selecting info about a product from a table called items and joining on a table called itemimages to get its associated images. The product can have more than one image. If i run the query on an item with 2 images i get 2 results for one item.....when theres only one item.....it seems to duplicate the item for each of its images....

SQL
SELECT items.*, itemimages.* FROM items INNER JOIN itemimages ON (items.itemID = itemimages.itemID) WHERE categoryID = '$category' AND active = 'yes' LIMIT $start, $limit"

Retrieve Everything AND Count Rows In One Query
set rsminmax = con.execute("SELECT * FROM `minirules_minmax` where RuleId = '" & contractId & "'")
set rsminmax2 = con.execute("SELECT COUNT(*) FROM `minirules_minmax` where RuleId = '" & contractId & "'")

Is there any way to do this in one SQL query?

How Do I Update Multiple Rows In One Query?
How do I update multiple rows in 1 query? I try not to do a loop of update queries.

Average Query With 2 Rows From Same Table
My table:
"answer"
answerID
answer(int)
questionID(int)
userID(int)

answer1 is questionID = 1
answer2 is questionID = 2
WHERE userID is the same for both answer1 and answer2

I want the average of answer1/answer2: AVG(ans1/ans2), but how?

Updating Multiple Rows With Same Fields (in One Query?)
I have 2 tables here

table categories

+--------------------------------------- +
| cat_id | cat_name | cat_total_articles |
+----------------------------------------+
| 1 | PHP | 23 |
+----------------------------------------+
| 2 | MySQL | 17 |
+----------------------------------------+
table articles

+---------------------------- +
| article_id | article_cat_id |
+-----------------------------+
| 1 | 1 |
+-----------------------------+
| 2 | 2 |
+-----------------------------+
Now I've changed an article's category from cat1 to cat2, and I need to update cat_total_articles of both cat1 (minus 1) and cat2 (plus 1) in category table.

Is it possible to combine the following queries into one statement?

PHP

mysql_query("UPDATE categories
SET cat_total_articles = cat_total_articles + 1
WHERE cat_id = 2");

PHP

mysql_query("UPDATE categories
SET cat_total_articles = cat_total_articles - 1
WHERE cat_id = 1");

Help With Updating Duplicate Rows In Mysql Query.
Quote:

SELECT id, count(*) AS numlist FROM products GROUP BY category, name, brands HAVING numlist > 1 ORDER BY id ASC

What it does is find the duplicates. I also have a column called "app" which has a default set to "1". So now what I want to do is that when duplicates are found, I want the first duplicate row in each group to stay as a "1" and the others in the same group to be updated to a "2". I need this done for every group.

How can I do this. I have looked high and low accrossed the web, but can't seem to find any solution.

I have managed to find out how to group them and count the number of listings in each group, but I can't seem to figure out how to do the rest.

Also, if possible, I would like it to be done with one query.

Query To Display The Rows As Colums Is Not Working!!
I want a select query to get two columns. but i need to get these columns one below the other that is consider that i have a table student in that i have 2 columns name mark.
Now i want the result as

Name Raj Rina Tina
Marks 80 90 70

I tried the --vertical option of mysql and G option in the query.

for example when i tried

select *from studentsG

it displayed

*********** 1. row ***********
name: Raj
marks : 80
********** 2. row ************
name: Rina
marks : 90
********** 3. row ************
name: Tina
marks : 70

but i want to display

Name Raj Rina Tina
Marks 80 90 70

Query Which Gets Rows Based On A Radius, Lon And Lat Doesn't Work?
I have found this query which gets rows based on a radius. I need this for zip codes based on lon and lat's.

$sql2 = "SELECT * FROM table as z WHERE (SQRT( (69.1 * (".$userLat." - z.lat)) * (69.1 * (".$userLat." - z.lat)) + (53.0 *(".$userLong." - z.lon)) * (53.0 *(".$userLong." - z.lon))) <= ".$userRadius." )";
return $sql2;
$res = mysql_query($sql2, $connDB) or die(mysql_error());
$row = mysql_fetch_assoc($res);
based on a test zip code gives a result like

SELECT * FROM table as z WHERE (SQRT( (69.1 * (52.399834 - z.lat)) * (69.1 * (52.399834 - z.lat)) + (53.0 *(4.840762 - z.lon)) * (53.0 *(4.840762 - z.lon))) <= 100 )
the lat and lon of the test zip code are right. As you can see z.lat and z.lon don't get any value. And these would be every lat and lon in table.

In my db table lon and lat are decimal(10,6) type with a default value of 0.000000

Why Does The Slow Query Log Show More Rows Than Exist?
# Time: 070528 17:14:57
# User@Host: counter[counter] @ localhost []
# Query_time: 3 Lock_time: 0 Rows_sent: 7 Rows_examined: 120647
SELECT SQL_CACHE `webpageUrl`, `webpageName`, COUNT(*) AS `count`, (COUNT(*) / (SELECT COUNT(*) FROM _1_log)) AS `pct` FROM _1_log GROUP BY `webpageUrl` ORDER BY `count` DESC LIMIT 7;

mysql> select count(*) from _1_log;
+----------+
| count(*) |
+----------+
| 111824 |
+----------+
1 row in set (0.00 sec)

Trying To Count The Number Of Rows In A Result Set After Query
The user fills out this form to sign up to the website, the form checks the database to see if the username has already been taken with the code:

$conn = mysql_connect("localhost:3306", "root", "********")
                                    or die ("Error With Connection");
        echo("connected<br><br>");
        $db_sel = mysql_select_db("game",$conn)
                                    or die ("Error With Database");
        $check = "select * from users where 'username' = '$username'";
        $db_sel = mysql_query($check,$conn)
                or die (mysql_error());

Creating Non-existent Rows In Query With Join
I want to make report using PivotTable/CrossTab and I used an application to create it.
The problem is, I want to so show NULL value to the temp table that will be the source of my report.

I'm using this query:

Omitting Characters Between Characters?
This is what I wrote:

select f.faqdesk_id
, c.categories_id
, f.faqdesk_question
, c.categories_name
, f.faqdesk_answer_short
from faqdesk_description as f
join faqdesk_to_categories as f2c
on f.faqdesk_id = f2c.faqdesk_id
join faqdesk_categories_description as c
on f2c.categories_id = c.categories_id

It works just fine, but the column: f.faqdesk_answer_short has characters I want to omit.
For example, the results have html formatting.

<H1>Hello World</H1><br><b>How is your day?</b>

I want to perform the search without viewing the html. Is there a way to omit the brackets & anything between them?

What Query To Check If Any Rows Exist Satisfying WHERE Clause?
I'm looking for a query that will check if any rows exists in a table according to a WHERE condition. I know I can use COUNT(*) but then mysql will do unnecessary task of counting all the rows whereas I just need true or false. So far I did this:

SELECT COUNT(*) AS exists FROM mytable WHERE ...
Sometimes I just select the first row and check later in php how many rows have been returned:

SELECT some_col FROM mytable WHERE ... LIMIT 1
But I cannot do this check (or can I?) in sql alone and I have problems when I want to use this in a subquery, for example:

SELECT id,
name,
(SELECT COUNT(*) FROM mytable WHERE ...) AS exists
FROM othertable
WHERE surname='xxx'
Can I do the same without using COUNT(*)? I would like a query that returns 0 or NULL if no rows were found, or 1 (or some other value) if 1 or more rows were found.

Count One Table's Rows From Multi Table Query
here are my tables (condensed)

FEEDS
feed_id
site_id

SITE
site_id
site_name

ARTICLES
article_id
feed_id
link

I want to create a query that returns the total number of articles for every site_id (which is unique in the SITE table). I have this:

PHP

$gsite = mysql_query("SELECT site.site_id, feeds.feed_id, COUNT(articles.article_id) AS acont FROM site,feeds,articles
WHERE feeds.site_id = site.site_id AND articles.feed_id = feeds.feed_id group by site.site_id", $connection)
or die(mysql_error());

The query does not use JOIN, ON and all that good stuff.

I just need the following variables to run through a loop:

site_id
the number of articles rows per site_id

LAST_INSERT_ID() Returns Zero
I'm executing a query like this:

MySQL
INSERT INTO `Members` (`UID`, `Username`, `Password`) VALUES(NULL, 'username', ?')

Immediately after, I'm trying to use mysqli_insert_id() from my PHP script. However, I keep getting a zero back from the function. I have used MySQL GUI Tools to verify that the row was definitely added, and it has a unique row number (40001 in my tests). I've tried recreating these queries manually from the GUI Tools to make sure it wasn't my script, and they encounter the same error. Even:

MySQL
SELECT LAST_INSERT_ID()
returns zero!

-----------------------

Here's how my table looks:

MySQL
CREATE TABLE `Database`.`Members` (
`UID` MEDIUMINT( 8 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`Username` VARCHAR( 32 ) NOT NULL ,
`Password` BINARY( 32 ) NOT NULL ,
UNIQUE (
`Username`
)
) ENGINE = INNODB

Server version: MySQL 5.0.32-Debian_7etch1-log via TCP/IP
GUI Tools Client Version: MySQL Client Version 5.1.11

LEFT() Returns Nothing
How comes this always return nothing even though i have data in the name field

SELECT Left(joueurs.name,1) AS newName FROM joueurs

LIMIT Returns
i have a table with 64K+ rows, a query like this:

select * from mails order by clave desc limit 1;

effectively returns the last record (key:64103) but if i try a query with a hight number as offset like this:

SELECT * FROM mails LIMIT 64000 , 30;

returns nothing, i've tried in the console and from phpmyadmin with the same result, a "select count(*) from mails;" query returns 64096.

does anybody knows what could be wrong? i've noticed mysql stops returning rows after ~21998 records

Limit Returns
Is there a way to do something in the WHERE clause that will only pull back entries in the DB from the last 7 days?
Could this work: WHERE field < NOW()-7 or something like that. Or could I do a php $date = date()-7 or seomthing like that?

What's Wrong With My Query To Filter Double Entries And Skip Empty Rows?
I am trying to get filter a database table.
- skip empty rows (i.e. ecardNameSender is empty)
- filter double entries

$sql = "SELECT COUNT(*) as total FROM tblEcards WHERE ecardNameSender != '' GROUP BY ecardEmailFriend";
$result = @mysql_query($sql, $connDB);
$row = mysql_fetch_assoc($result);
$totalPics = $row['total'];
echo $totalPics;
What's wrong with my query?

How To Get 5 Random Returns From Mysql?
I have a table that stores username and image_id. Like -

user_name | image_id

user_1 | image_1
user_2 | image_2
user_3 | image_33
user_2 | image_56 etc.

I need to get 5 random image_ids, each from a different user. I was considering using rand(), but google told me that this will slow down the sql server, bigger the database.

PHP

SELECT DISTINCT user_name, image_id FROM tablename ORDER BY RAND() LIMIT 5

SQL Select Statement Returns Nothing?
When I view this page, nothing appears in the browser

PHP

while($row = mysql_fetch_array($result)){$Identifier = $row['ID'];$random_thumb_query = mysql_query(" SELECT userid, filename FROM photo_photoplog_fileuploads WHERE moderate = 0 AND userid = '$Identifier' ORDER BY dateline DESC LIMIT 1") or die(mysql_error());$random_thumb_row = mysql_fetch_array($random_thumb_query);echo $random_thumb_row;$random_thumb_nail = '<img src="/photoplog/images/'.$random_thumb_row['userid'].'/small/'.$random_thumb_row['filename'].'" alt="" border="0" />'

http://miamiasp.com/providers.php
u:luke
x: urtnowski
is the $Identifier variable ok?

Getting The Row Where Forumula Returns Smallest Value
i am trying to select one particular row of data from a table which has the least difference between two other columns from another table.
i have been looking at the documentation and keep trying. this is the closest i could get but it is still not right.

select * from photographs group by ( select min(p.rate-p.comments) from photorates as p )
this could sometimes return me multiple rows and if i were to change it to:

select * from photographs group by ( select max(p.rate-p.comments) from photorates as p )
having max instead of min, i get the min and max rows of data. i suppose i should only get one row of data since i need either the least or most difference from the math.

LIMIT 5 Only Returns 2 When Using GROUP_CONCAT
I'm trying to limit my results in the following query. When I make this query without the GROUP_CONCAT lines, I get the number of results I specified in LIMIT, when I put those lines back in, I get less than the number of results I specify with LIMIT....

Select * Returns No Data
I have a problem where if I issue a select * from against a database it returns no data,

but if I select column from it returns the data.

Why would the * not be working as a wildcard?

Troubleshooting Carriage Returns In Csv
I am exporting data from filemakerpro in a csv file and uploading it via cocoamysql/phpmyadmin, from which it is viewable online through dynamic pages. Carriage returns in Filemaker are translating through as small boxes (firefox), small boxes with X's inside (safari); in the csv file they are tranlating as either spaces or nothing (just jams the words together where there should be a return).

Any ideas on how to eliminate this problem? or a direction to look in?

Found_rows() Everytime Returns 1
I use this query
SELECT SQL_CALC_FOUND_ROWS * FROM prods LIMIT 100,10;

I'd like to know how many rows this query returns without limit, but
using this
select FOUND_ROWS();
the result is everytime 1

You know why?
No other query are processed before the found_rows().

Import CSV With Carriage Returns
How do I use the import function in the phpMyAdmin to import a CSV file that contains carriage returns. Or is there a better format to import data with carriage returns.

CONCAT () Returns BLOB
MySQL ver. 3.23.49

SELECT tblHrsClass.classid , CONCAT( tblHrsClass.classTtl ,' ', tblHrsClass.classDate ) class_ttl FROM tblHrsStudent LEFT JOIN tblHrsClass ON tblHrsStudent . classid =tblHrsClass.classid GROUP BY tblHrsClass . classid HAVING COUNT(tblHrsStudent . classid )<19

The query above is broke. I'm using it to build a list box.

I can run the query without the CONCAT function and pulls the expected data.

When I add CONCAT( tblHrsClass.classTtl ,' ', tblHrsClass.classDate ) class_ttl,

class_ttl is returned as a BLOB of a certain size.

Use Where In Query So It Returns Rows That Has Less Than 10 Characters In The Word

I want to return rows that has less than 10 characters in the word that is used in the where clause sort of thing.I can't really explain so I will give you an example, which is obviously wrong.Quote:mysql_query("select games from gaming where gamename < 10 characters");

I want to return rows that has less than 10 characters in the word that is used in the where clause sort of thing.

I can't really explain so I will give you an example, which is obviously wrong.

Quote:

mysql_query("select games from gaming where gamename < 10 characters");