SELECT
u.userid, u.username,
(SELECT count(*) FROM attachment as a WHERE a.userid=u.userid) as amount
FROM
user
WHERE
AND u.usergroupid=10
ORDER BY amount DESC
$activeQuery = mysql_query("SELECT count(`status`) AS `active` FROM `assignments` WHERE `user` = $user_id AND `status` = 0");
$active = mysql_fetch_assoc($activeQuery);
$failedQuery = mysql_query("SELECT count(`status`) AS `failed` FROM `assignments` WHERE `user` = $user_id AND `status` = 1");
$failed = mysql_fetch_assoc($failedQuery);
$completedQuery = mysql_query("SELECT count(`status`) AS `completed` FROM `assignments` WHERE `user` = $user_id AND `status` = 2");
$completed = mysql_fetch_assoc($completedQuery);
There has to be a better way to do that, right? I don't know how much I need to elaborate as you can see what I'm trying to do, but is there any way to do all of that in one query? I need to be able to output the active, failed, and completed assignments, preferably in one query.
I want to remove password for user root in localhost how can I do that?by mistake I have set the password of root user thats why phpmyadmin is giving error-
#1045 - Access denied for user 'root'@'localhost' (using password: NO)
i know that if you create a foreign key on a field (parent_id) in a child table that refer to a parent table's primary key (id), then if this parent table is deleted the child class will be deleted as well if you set onDelete to cascade when creating the foreign key in the child class.
but what happens if i set it to onUpdate = cascade?
i have date in dd/mm/yyyy format. how can i store it in databse, if i fant to do some operations on them after?
for example i must find out the rows, where date > something
what type i must set to date field?
thanks
This time my setup looks like this: one table with galleries names (gallery_id, gallery_name) and another table with galleries photos (photo_id, photo_gallery_id, photo_name).
What I need is to get all the galleries with one random picture for each gallery.
Is it possible to do this with a single query?
SELECT categories.*, COUNT(categoryID) AS kritCount
FROM categories AS categories
LEFT JOIN krits ON categories.id = categoryID
WHERE (krits.approved = '1')
GROUP BY categories.id
So this works great except that it does not return a category that has a 0 count of krits in the category.
It will if I remove the WHERE statement but I need the WHERE to only select the krits where the field approved = 1
I have a problem in the code.i have two tables 'sms' and 'bd_paid_bribe'.sms table has a column 'Message' and bd_paid_bribe table has a column 'c_addi_info'.when i execute the code first time all the values of Message column are inserted into c_addi_info column.when i enter a record for the second time instead of inserting the new record, all the records of Message column are inserted into bd_paid_bribe column.can u modify the code and provide a solution to avoid duplication and to insert only the newly added record.
<?php
$con=mysql_connect('localhost','root','');
if(!$con)
{
die("couldn't connect");
}
mysql_select_db("ipab2",$con);
$rs2=mysql_query(" select max(sms_index) from tab3");
do
{
$rs=mysql_query("insert into tab3(sms_index)select max(sms_index) from sms");
$rs3=mysql_query("SELECT max(sms_index) FROM sms");
$rs1=mysql_query("insert into bd_paid_bribe(c_addi_info) select
Message from sms ");
}while($rs2>$rs3);
?>
I need a query to return this result:
+---------+-----+-------+
| ref_nid | nid | delta |
+---------+-----+-------+
| AA | 97 | 1 |
| BB | 97 | 2 |
| CC | 97 | 3 |
| DD | 98 | 1 |
| EE | 98 | 2 |
| FF | 98 | 3 |
+---------+-----+-------+
However, I do not have the delta column. I need to generate it for each nid group.
In other words, I need an auto incremented number for each group of the result.
I'm not sure exactly why this happened, but I'm assuming it was an dump and import. The db is full of characters like — for commas and such. I've tried various solutions on the web, but nothing seems to work. I've verified that the html header specifies utf8.
Any ideas on how I can get the entire db back to normal characters?
I have a large list of phrases / quotes / sentences in a text file. Mixed alphanumeric text, it hasn't been screened for "'s or ''s.
Is there an easy way to throw this into a table?
Hi
I have a table with the following structure:
id int(11),
name varchar(250)
I have lots of records in the table but when I am trying to find a particluar record which has the following value on the name field:
Lorem ipsum d\'olor sit amet
The query is simply returning a blank recordset. I am not being able to figure out this weird behaviour, when my query is as simple as follows:
SELECT * FROM slot_games WHERE name='Lorem ipsum d\'olor sit amet'
Would appreciate your help please! Thanks in advance.
"SELECT id as Id from dbTable WHERE code = ? AND CURDATE() BETWEEN
start_date AND end_date AND offerId IN ('12321', '12124')";
//Passing arguments for the query
$args = array_merge(array(51342),$offerid);
//Execute the prepared query
$statement->execute($args);
Now array(51342) represents combination of code+value, aside my database has value, code columns
and so I want a query which would look logically like
"SELECT id as Id from dbTable WHERE code and value
(Note here I do not know the syntax, what am looking at is (code+value = ?), please advise on query) = ?
AND CURDATE() BETWEEN start_date AND end_date AND offerId IN ('12321', '12124')";
I want to insert into multiple tables in same query by using BEGIN and COMMIT.
It seems the error occur at begin.
here is my SQL command
BEGIN
INSERT INTO Product (pName, pBrand, pCategory, pSize, pQuantity, pPrice, pDetail)
VALUES('$name', '$brand', '$category', '$size', '$quantity', '$price', '$detail')
INSERT INTO Image (iName, iExt, iSize, pID)
VALUES('$img_name', '$img_ext', '$img_size', LAST_INSERT_ID());
COMMIT;
Hi everybody,
What's the most appropriate (read least data consuming) data field to store a true/false/1/0 value in a mySQL database?
I have previously used a one character long tinyint, but I am not sure if it's the best solution?
Thanks!
How do I drop selected tables in Rails? I want to drop all tables from a database with a given prefix. PHPMyAdmin would be very useful at this point.
Thanks
I have the following tables:
posts (post_id, content, etc)
comments (comment_id, post_id, content, etc)
posts_categories (post_category_id, post_id, category_id)
and this query:
SELECT `p`.*, COUNT(comments.comment_id) AS cmts, posts_categories.*,comments.*
FROM `posts` AS `p`
LEFT JOIN `posts_categories`
ON `p`.post_id = `posts_categories`.post_id
LEFT JOIN `comments`
ON `p`.post_id = `comments`.post_id
GROUP BY `p`.`post_id`
There are three comments on post_id=1 and four in total. In posts_categories there are two rows, both assigned to post_id=1. I have four rows in posts.
But if I query the statement above I get a result of 6 for COUNT(comments.comment_id) at post_id=1. How is this possible? I guess the mistake is somewhere in the GROUP BY clause but I can't figure out where.
Any suggestions?
I've a table with a datetime (format: 'Y-m-d H:i:s') 'created' field and 'amount' (integer) field in each row. Now I want to find out total 'amount' in last year month wise. How can I do this?
EDIT
I made an edit to clarify the actual problem.
Say I have these tables:
people(id, name),
cars(person_id, car)
and this query:
SELECT c.car
FROM people as p, cars as c
WHERE c.person_id = p.id
AND p.id = 3
I want the c.car column to take its name from the name field in the people table, like this (invalid SQL, just to illustrate):
SELECT c.car AS(SELECT name FROM people WHERE id = 3)
How do I do that?
i have user table, i have 5 records ,
i delete two records , then executed the rollback command, rollback command executed successfully.
But that deleted two records not recovered ,
user table engine is innodb...
I have two tables in my database, one contains a list of items with other information on these items. The other table is contains a list of photographs of these items.
The items table gives each item a unique identifier,which is used in the photographs table to identifier which item has been photographed.
I need to output a list of items that are not linked to a photograph in the second table. Any ideas on how I can do this?