When I create a view I am basically like making a new table that will automatically be transacted upon when data in one of the tables it joins changes?
Also why can't I use subqueries in my view????
I am doing a query with three tables, the problem was one table has many occurrences of id of another.
sample data:
users: id
answers:
id:1
user_answer :1
id:1
user_answer :2
id:1
user_answer :3
Questions:
id:1
answers :answer description
id:2
answers :answer description
id:3
answers :answer description
How can I get all user information and all answer and its description, I used GROUP by user.id but it only returns only one answer.
I want to return something like this list all of users answer:
Name Q1 Q2
USERNAME ans1,ans2 ans1,ans2 comma separated description of answer here
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!
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 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 ?
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?
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.
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 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 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 ");
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?
i am doing a delete with a LIKE statement
my keybuffer is 25m, the sort buffer size is 256k
the delete has been taking over 2 hours
should i increase memory usage? there are about 50 megs of data in the table from which i am deleting, thats about 500,000 rows
is there anything else i can do on the adminsitration size to speed up this delete?
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.
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 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 trying to install magento (open source e-commerce platform) sample data on my webhost.
I have uploaded the file magento_sample_data.sql via ftp, and setup a new database and have assigned it to a user.
How do I get the sample data into my empty database?
I am trying to output data from
table : artists
row : artist
into this format.
Artist Names can have special characters and there are over 16k of them.
It needs to be written to a file. called anything artist.php for example
$Artist = array(
"Name from database",
"Name from database",
"Name from database",
"Name from database",
"Name from database"
);
ok sorry for not explaining. do this for ajax auto complete.. so i need to create a file with this array in it.
here is the exact script
http://www.brandspankingnew.net/specials/ajax_autosuggest/ajax_autosuggest_autocomplete.html
UPDATE counter_reports
SET `counter`=`counter`+1,`date`=?
WHERE report_id IN(
(SELECT report_id FROM counter_reports WHERE report_name="emails_sent" AND `year`=1 ORDER BY report_id DESC LIMIT 1),
(SELECT report_id FROM counter_reports WHERE report_name="emails_sent" AND `month`=1 ORDER BY report_id DESC LIMIT 1),
(SELECT report_id FROM counter_reports WHERE report_name="emails_sent" AND `week`=1 ORDER BY report_id DESC LIMIT 1),
(SELECT report_id FROM counter_reports WHERE report_name="emails_sent" AND `day`=1 ORDER BY report_id DESC LIMIT 1)
)
Is there any alternative for such sql? I need to update(increment by 1) last counter reports for day,week,month and year.
If I'm adding manually, sql works fine, but with subqueries it fails to launch.
Thanks. :)
I've use following code
$con = mysql_connect("localhost","root","");if (!$con) {die('Could not connect: ' . mysql_error());}
It's works. but when
$db_host='localhost';$db_id='root';$db_pass='';
$con = mysql_connect($db_host,$db_id,$db_pass);if (!$con) {die('Could not connect: ' . mysql_error());}
it didn't works, trying to swap ("),('),and empty in mysql_connect() and in $var and vice versa. Any help would be appreciated. Thanks.
How can I use the first query's id value $row['id'] again after I run a second query inside the while loop statement? To show you what I mean here is a sample code below of what I'm trying to do.
I hope I explained it right.
Here is the code.
$mysqli = mysqli_connect("localhost", "root", "", "sitename");
$dbc = mysqli_query($mysqli,"SELECT users.*
FROM users
WHERE user_id = 4");
if (!$dbc) {
// There was an error...do something about it here...
print mysqli_error($mysqli);
}
while($row = mysqli_fetch_assoc($dbc)) {
echo '<div>User: ' . $row['id'] . '</div>';
echo '<div>Link To User' . $row['id'] . '</div>';
$mysqli = mysqli_connect("localhost", "root", "", "sitename");
$dbc2 = mysqli_query($mysqli,"SELECT COUNT(cid) as num
FROM comments
WHERE comments_id = $row[id]");
if (!$dbc2) {
// There was an error...do something about it here...
print mysqli_error($mysqli);
} else {
while($row = mysqli_fetch_array($dbc2)){
$num = $row['num'];
}
}
echo '<div>User ' . $row['id'] . ' Comments# ' . $num . '</div>';
}
Okay I use this script here to make a backup of my database:
mysqldump -u root -h localhost -pPASSWORD forums | gzip -9 > backup-$(date +%Y-%m-%d).sql.gz
This is used in a cron daily.
But I need to download this remotely or through a ftp program every day as well so I have a physical copy of it on my home hard drive, is this possible? I know it is, can anyone tell me a quick way to do it?
My 1st post, but i got many great answers and tips from stackoverflow so far. This one was a close call- How does facebook, gmail send the real time notification? but not exactly, so let brainstorm this together.
I have CMS system with mail notification when a change is made on the site. Everything wotk very well but i want to prevent multiple notification if somene make another quick change to, let say, fix a typo. Using php mail(), obviously.
I've tough of 2 ways, one simple, and one.... let just say, pretty heavy... cough. the 3rd one was inspired by Implementing Email Notification but really doesn't look appealing to me to send bunch of email at once.
Use a timestamp to check if another change was made in the 'let say' last 5 minutes.
Record the last change, and compare it to the new one. Could be usefull for backup at the same time since i'll have to save the change somewhere, but text can be long and making an sql search would be painfull. Wouldn't it?
Use cron to send changes every x minutes... convince me if you ythink it is a suitable solution.
Any ideas, comment or suggestion of your own? Looking forward for your inputs, and since i now registered, i'll do my best to help around.
Cheers, all
llt
I have been looking for a while now but I can not find an easy solution for my problem. I would like to duplicate a record in a table, but of course, the unique primary key needs to be updated.
I have this query:
INSERT INTO invoices
SELECT * FROM invoices AS iv WHERE iv.ID=XXXXX
ON DUPLICATE KEY UPDATE ID = (SELECT MAX(ID)+1 FROM invoices)
the problem is that this just changes the ID of the row instead of copying the row. Does anybody know how to fix this ?
Thank you verrry much,
Digits
//edit: I would like to do this without typing all the field names because the field names can change over time.