Hi ,
i have employee table and its empid length is INT 3 ,
But it accepting more then 3 character ....
How it is possible
(THIS IS TESTED IN PHPMYADMIN)
Why is that when I search a row in my database, it ended up 0 results? In fact, there are rows that meet my search criterion when I view them manually, but the search button doesnt work as it is. Heres how
you can see there are rows that have '0000-00-00 00:00:00' in them, but when I used the search feature, it ended up like this:
take note that Im entering it in the right field which is 'AcctStopTime'.
TIA
I am trying to write a query to select all records from users table where User_DateCreated (datetime field) is = 3 months from today.
Any ideas? Thanks!
Hello,
Why the two query below return duplicate member_id and not the third ?
i need the second query to work with distinct.
Anytime i run a GROUP BY, this query is incredibly slow and the resultset doesn't return the same value as distinct (the value is wrong).
SELECT member_id, id
FROM ( SELECT * FROM table1 ORDER BY created_at desc ) as u
LIMIT 5
+-----------+--------+
| member_id | id |
+-----------+--------+
| 11333 | 313095 |
| 141831 | 313094 |
| 141831 | 313093 |
| 12013 | 313092 |
| 60821 | 313091 |
+-----------+--------+
SELECT distinct member_id, id
FROM ( SELECT * FROM table1 ORDER BY created_at desc ) as u
LIMIT 5
+-----------+--------+
| member_id | id |
+-----------+--------+
| 11333 | 313095 |
| 141831 | 313094 |
| 141831 | 313093 |
| 12013 | 313092 |
| 60821 | 313091 |
+-----------+--------+
SELECT distinct member_id
FROM ( SELECT * FROM table1 ORDER BY created_at desc ) as u
LIMIT 5
+-----------+
| member_id |
+-----------+
| 11333 |
| 141831 |
| 12013 |
| 60821 |
| 64980 |
+-----------+
my table sample
CREATE TABLE `table1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`member_id` int(11) NOT NULL,
`s_type_id` int(11) NOT NULL,
`created_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `s_FI_1` (`member_id`),
KEY `s_FI_2` (`s_type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=313096 DEFAULT CHARSET=utf8;
I have problem querying table with variable in IN function.
SELECT
s.date,
(SELECT
GROUP_CONCAT(value)
FROM value
WHERE id_value
IN(s.ref_values)
)
AS vals
FROM stats s
ORDER BY s.date DESC
LIMIT 1
Where s.ref_values is '12,22,54,15'. I get only one return for first number (12).
When I insert that value directly in IN(12,22,54,15) it finds all 4.
So, there must be problem with using variable in IN. What am I doing wrong?
I'm way out of my league here...
I have a mapping table (table1) to assign particular values (value) to a whole number (map_nu). My second table (table2), is a collection of averages (avg)
(I couldn't figure out how to properly make a markdown table, please feel free to edit!)
table1: table2:
(value)(Map_nu) (avg)
---- -----
1 1 1.111
1.045 2 1.2
1.09 3 1.33333
1.135 4 1
1.18 5 1.389
1.225 6 1.42
1.27 7 1.07
1.315 8
1.36 9
1.405 10
I need to find a way to match the averages from table2 to the closest value in table1. It only need to match to the 2 digit past the decimal, so I've added the Truncated function
SELECT map_nu
FROM `table1`
JOIN table2 ON TRUNCATE(table1.value,2)=TRUNCATE(table2.avg,2)
I still miss the values that don't match the averages exactly. Is there a way to pick the nearest truncated value?
Thanks!
I'm trying to make an OO Login system for a project I'm working on, and am having trouble with inserting variables into the query strings. In the code below, if I replace "$TBL_NAME" with the actual table name it works. Why isn't $TBL_NAME translating to the value of $TBL_NAME?
class UserDB {
private $TBL_NAME = "users";
public static function CheckLogin($username, $password) {
Database::Connect();
$username = stripslashes($username);
$password = stripslashes($password);
$username = mysql_real_escape_string($username);
$password = mysql_real_escape_string($password);
$sql="SELECT uid FROM $TBL_NAME WHERE username='$username' AND password='$password' ";
$result =mysql_query($sql);
$count=mysql_num_rows($result);
if ($count==1)
return true;
else
return false;
}
The Query is returning false.
ID
NAME
AMT
1
Name1
1000
2
Name2
500
3
Name3
3000
4
Name1
5000
5
Name2
2000
6
Name1
3000
consider above table as sample.
am having a problem in my sql query, Am using like this.
Select name,amt from sample where amt between 1000 and 5000
it returns all the values in the table between 1000 and 5000, instead
I want to get maximum amount record for each name
i.e.,
3 name3 3000
4 name1 5000
5 name2 2000
I'm running two load balanced servers for one website, and I'd like the databases to be synchronized. Queries may be run on either of the two servers because they are both production sites, so the replication can't just work one way.
It doesn't have to be in real-time, just fairly accurate so people don't notice a difference when they get switched to a different server.
I have 3 tables. Here is the relevant information needed for each.
items
prod_id
order_id
item_qty
orders
order_id
order_date
order_status
acct_id
accounts
acct_id
is_wholesale
items is linked to order by the order_id and orders is linked to accounts via acct_id
I need to sum item_qty for all items where prod_id=464 and the order stats is not 5 and where the is_wholesale is 0 and the order_date is between two dates. Im struggling with this and would appreciate any help. Here is what I have but it's not working correctly:
SELECT SUM(items.item_qty) as qty
FROM items
LEFT JOIN orders ON orders.order_id = items.order_id
LEFT JOIN accounts on orders.acct_id = accounts.acct_id
WHERE items.prod_id =451
AND orders.order_date >= '$from_date'
AND orders.order_date <= '$to_date'
AND orders.order_status <>5
AND accounts.is_wholesale=0;
Again, any help would be greatly appreciated!
I have following table :
id dateStart dateEnd active
1 2012-11-12 2012-12-31 0
2 2012-11-12 2012-12-31 0
I want to compare todays date in between dateStart and dateEnd.
Following is my query for this :
$todaysDate="2012-26-11";
$db = Zend_Registry::get("db");
$result = $db->fetchAll("SELECT * FROM `table` WHERE active=0 AND {$todaysDate} between dateStart and dateEnd");
return $result;
But its not working.
Any solution.
Thanks in advance.
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.
Hi guys, thanks in advance for any help on this topic!
I'm sure this has a very simply answer, but I can't seem to find it (not sure what to search on!). A standard count / group by query may look like this:
SELECT COUNT(`t2`.`name`)
FROM `table_1` `t1`
LEFT JOIN `table_2` `t2` ON `t1`.`key_id` = `t2`.`key_id`
GROUP BY `t1`.`any_col`
and this works as expected, returning 0 if no rows are found. So does:
SELECT COUNT(`t2`.`name`)
FROM `table_1` `t1`
LEFT JOIN `table_2` `t2` ON `t1`.`key_id` = `t2`.`key_id`
WHERE `t1`.`another_column` = 123
However:
SELECT COUNT(`t2`.`name`)
FROM `table_1` `t1`
LEFT JOIN `table_2` `t2` ON `t1`.`key_id` = `t2`.`key_id`
WHERE `t1`.`another_column` = 123
GROUP BY `t1`.`any_col`
only works if there is at least one row in table_1 and fails miserably returning an empty result set if there are zero rows. I would really like this to return 0! Anyone enlighten me on this? Beer can be provided in exchange if you are in London ;-)
I have an query like:
SELECT id as OfferId FROM offers
WHERE concat(partycode, connectioncode) = ?
AND CURDATE() BETWEEN offer_start_date
AND offer_end_date AND id IN ("121211, 123341,151512,5145626 ");
Now I want to cache the results of this query using memcache and so my question is
How can I cache an query using memcache.
I am currently using CURDATE() which cannot be used if we want to implement caching and so how can I get current date functionality without using CURDATE() function ?
Cannot add or update a child row: a foreign key constraint fails (`mydb`.`job_listing_has_employer_details`, CONSTRAINT `job_listing_has_employer_details_ibfk_2` FOREIGN KEY (`employer_details_id`) REFERENCES `employer_details` (`id`))
INSERT INTO `job_listing_has_employer_details` (`job_listing_id`, `employer_details_id`) VALUES (6, '5')
What does this mean? The two ID's I am inserting into the table exsist.
Hello!
How do i get the actual max length of a specified column in php?
For instance, this table:
id - int(11)
name - string(20)
I want in php to select the maximum number of characters that a field can have, like
SELECT length(name) from table1
and it should then return 20 (since its the maximum number of characters for that field).
I have a mysq query which gets including some vars like that:
messages TABLE receiver cols
user1 rows : 1,3,5
user2 rows : 2,3
user3 rows : 1,4
I want to get rows which includes '3' value. So I will get 'user1' and 'user2'.
I tried that but naturally it doesn't work.
mysql_query("SELECT * FROM messages WHERE receiver='3'");
How can I do this?
I keep getting the same number outputted for the Total Sales, Minimum Sale, Largest Sale and Average Sale.
The Total Invoices is working perfectly, but I cant seem to figure out how to fix the other ones.
Here's the query:
SELECT SUM( b.`Number of Invoices`) AS `Total Invoices`,
SUM( b.`Total Customer Purchases`) AS `Total Sales`,
MIN( b.`Total Customer Purchases`) AS `Minimum Sale`,
MAX( b.`Total Customer Purchases`) AS `Largest Sale`,
AVG( b.`Total Customer Purchases`) AS `Average Sale`
FROM (SELECT a.CUS_CODE,
COUNT(a.`Number of Invoices`) AS `Number of Invoices`,
SUM(a.`Invoice Total`) AS `Total Customer Purchases`
FROM ( SELECT CUS_CODE,
LINE.INV_NUMBER AS `Number of Invoices`,
SUM(LINE.LINE_UNITS * LINE.LINE_PRICE) AS `Invoice Total`
FROM `ttriggs`.`INVOICE`, `ttriggs`.`LINE`
WHERE INVOICE.INV_NUMBER = LINE.INV_NUMBER
GROUP BY CUS_CODE, LINE.INV_NUMBER
) a
) b
GROUP BY b.CUS_CODE;
Heres the database diagram
https://www.dropbox.com/s/b8cy5l29jwh8lyv/1_edit.jpg
Subquery generates:
CUS_CODE 10011
Number of Invoices 8
Total Customer Purchases 1119.03
Any help is greatly appreciated,
Thanks!
I have a DB with a table that is named "victim". The form that dumps the info into the table has room for two victims and therefore there is vic1_fname, vic1_lname, vic2_fname, vic2_lname, etc.. (business name, person first, person last, address, city, state, zip) a "1" and "2" of each. Now I want to search the DB and locate listed victims.
This is what I have so far:
$result = mysql_query(
"SELECT victim.*
FROM victim
WHERE vic1_business_name OR vic2_business_name LIKE '%$search_vic_business_name%'
AND vic1_fname OR vic2_fname LIKE '%$search_vic_fname%'
AND vic1_lname OR vic2_lname LIKE '%$search_vic_lname%'
AND vic1_address OR vic2_address LIKE '%$search_vic_address%'
AND vic1_city OR vic2_city LIKE '%$search_vic_city%'
AND vic1_state OR vic2_state LIKE '%$search_vic_state%'
AND vic1_dob OR vic2_dob LIKE '%$search_vic_dob%'
");
<table width="960" style="border: groove;" border=".5">
<tr><th colspan=10>You search results are listed below:</th></tr>
<tr>
<th>Case Number</th>
<th>Business Name</th>
<th>First Name</th>
<th>Last Name</th>
<th>DOB / Age</th>
<th>Address</th>
<th>City</th>
<th>State</th>
</tr>
<?php
while($row = mysql_fetch_array($result))
{ ?>
<tr>
<td align="center"><?php print $row['vic_business_name']; ?></td>
<td align="center"><?php print $row['vic_fname']; ?></td>
<td align="center"><?php print $row['vic_lname']; ?></td>
<td align="center"><?php print $row['vic_dob']; ?></td>
<td align="center"><?php print $row['vic_adress']; ?></td>
<td align="center"><?php print $row['vic_city']; ?></td>
<td align="center"><?php print $row['vic_state']; ?></td>
</tr>
<?php } ?>
</table>
The info did not display in the table until I changed the table to this:
<tr>
<td align="center"><?php print $row['vic1_business_name']; ?></td>
<td align="center"><?php print $row['vic1_fname']; ?></td>
<td align="center"><?php print $row['vic1_lname']; ?></td>
<td align="center"><?php print $row['vic1_dob']; ?></td>
<td align="center"><?php print $row['vic1_adress']; ?></td>
<td align="center"><?php print $row['vic1_city']; ?></td>
<td align="center"><?php print $row['vic1_state']; ?></td>
</tr>
<tr>
<td align="center"><?php print $row['vic2_business_name']; ?></td>
<td align="center"><?php print $row['vic2_fname']; ?></td>
<td align="center"><?php print $row['vic2_lname']; ?></td>
<td align="center"><?php print $row['vic2_dob']; ?></td>
<td align="center"><?php print $row['vic2_adress']; ?></td>
<td align="center"><?php print $row['vic2_city']; ?></td>
<td align="center"><?php print $row['vic2_state']; ?></td>
</tr>
Now it displays both rows, even if its empty. It doesn't matter if the victim was listed originally as vic1 or vic2, i just want to know if they are a victim.
I hope this makes sense. I can't get it to display the way I want, line-by-line, irregardless of whether you are vic1 or vic2.
I am calling the results form a query to a list on my site based on if the item is "downtown_hosted". This works fine but I would now like to sort that list DESC, but can't seem to get the syntax correct.
Below is what I have:
$result_events = mysql_query("SELECT * FROM events WHERE downtown='downtown_hosted' ORDER BY date DESC LIMIT 5 ");
Hi,
I inserted a number of rows 3 hours ago and I don't want these rows from changing. How can I write sql statement that compare current time with the timstamp in the row and restrict users from changing it if abouve creteria is met.
Thanks
I am working on a widget that is a lot like twitters widget where there is a list of postings and a view more button. I can get it to work with using ID variables but I would like to sort by popular posts.
Here is my mysq code:
$sql = "SELECT id, title, category, icon_normal, status, description, views_monthly FROM posts WHERE views_monthly<=".$lastPost." AND status='1' ORDER BY views_monthly DESC LIMIT 9"
So the problem that I am having is it shows the first 9 just fine. When it gets to the point where views_monthly = 0 then it just loads the same 9 post over again.
How do it get it to switch to using ID when it reaches Views_monthly = 0 and load fresh posts?
Hi,
I have series of records in a table called 'hits' and each record has the current_timestamp (ie. 2010-04-30 10:11:30) in a column called 'current_time'.
What I would like to do is query these records and return only the records from the current month. I cannot seem to get this to work.
I have tried a range of queries that don't work such as -
Select * FROM hits WHERE MONTH(FROM_UNIXTIME(current_time)) = 4
I don't know if I am even on the right lines!
Can anyone point me in the right direction?
Cheers.
In my query:
$cselect = mysql_real_escape_string($_POST['cselect']);
---------------
---------------
$sql = sprintf("INSERT INTO content
(id, catID, title, abstract, body, status, published, date, description_meta, keywords_meta)
VALUES ('', '%s', '%s','%s','%s','%s','%s','%s','', '' )", $cselect,$chead, $cabst,$ctext, $cp, $cradio, 'TIMESTAMP: Auto NOW()');
ouptput for date is: 0000-00-00 00:00:00. What is wrong in my query?
Thanks in advance
Hi,
I am trying to make a dynamic form which retrives data. How can I use javascript to load a value from a mysql database?
I understand that it must use php in some way, and I know how to query the database and assign the value to a php variable, but I have no idea what to do after that...any advice?
the goal is to have someone enter their orderid say, then the datbase auto pulls down and enters their last invoice in the field below it after they click GO...