How do you use cakephp to count, for example the number of posts, made every month in a year?
Preferably using Model-find('count') and get the data in an array.
I am creating a very simple script. The purpose of the script is to pull a question from the database and display all answers associated with that particular question. We are dealing with two tables here and there is a foreign key from the question database to the answer database so answers are associated with questions.
Hope that is enough explanation. Here is my code. I was wondering if this is the most efficient way to complete this or is there an easier way?
<html>
<head>
<title>Advise Me</title>
<head>
<body>
<h1>Today's Question</h1>
<?php
//Establish connection to database
require_once('config.php');
require_once('open_connection.php');
//Pull the "active" question from the database
$todays_question = mysql_query("SELECT name, question
FROM approvedQuestions
WHERE status = active")
or die(mysql_error());
//Variable to hold $todays_question aQID
$questionID = mysql_query("SELECT commentID FROM approvedQuestions
WHERE status = active")
or die(mysql_error());
//Print today's question
echo $todays_question;
//Print comments associated with today's question
$sql = "SELECT commentID FROM approvedQuestions WHERE status = active";
$result_set = mysql_query($sql);
$result_num = mysql_numrows($result_set);
for ($a = 0; $a < $result_num; $a++)
{
echo $sql;
}
?>
</body>
</html>
Here are my tables
respondents:
field sample value
respondentid : 1
age : 2
gender : male
survey_questions:
id : 1
question : Q1
answer : sample answer
answers:
respondentid : 1
question : Q1
answer : 1 --id of survey question
I want to display all respondents who answered the certain survey, display all answers and total all the answer and group them according to the age bracket.
I tried using this query:
$sql = "SELECT
res.Age,
res.Gender,
answer.id,
answer.respondentid,
SUM(CASE WHEN res.Gender='Male' THEN 1 else 0 END) AS males,
SUM(CASE WHEN res.Gender='Female' THEN 1 else 0 END) AS females,
CASE
WHEN res.Age < 1 THEN 'age1'
WHEN res.Age BETWEEN 1 AND 4 THEN 'age2'
WHEN res.Age BETWEEN 4 AND 9 THEN 'age3'
WHEN res.Age BETWEEN 10 AND 14 THEN 'age4'
WHEN res.Age BETWEEN 15 AND 19 THEN 'age5'
WHEN res.Age BETWEEN 20 AND 29 THEN 'age6'
WHEN res.Age BETWEEN 30 AND 39 THEN 'age7'
WHEN res.Age BETWEEN 40 AND 49 THEN 'age8'
ELSE 'age9'
END AS ageband
FROM Respondents AS res
INNER JOIN Answers as answer ON answer.respondentid=res.respondentid
INNER JOIN Questions as question ON answer.Answer=question.id
WHERE answer.Question='Q1' GROUP BY ageband ORDER BY res.Age ASC";
I was able to get the data but the listing of all answers are not present. What's wrong with my query.
I want to produce something like this:
ex: # of Respondents is 3 ages: 2,3 and 6
Question: what are your favorite subjects?
Ages 1-4:
subject 1: 1
subject 2: 2
subject 3: 2
total respondents for ages 1-4 : 2
Ages 5-10:
subject 1: 1
subject 2: 1
subject 3: 0
total respondents for ages 5-10 : 1
I am about to upload my site to the Server, and I have ubuntu 9.1 as OS.
Now, I have installed LAMP, and need to somehow password protect my website.
How can I do this so only I have access to my website while developing it?
Please be thorough... Thanks
I have a table which consists of heavy blobs, and I wanted to conduct some tests on it.
I know deleted space is not reclaimed by innodb, so I decided to reuse existing records by updating its own values instead of createing new records.
But I noticed, whether I delete and insert a new entry, or I do UPDATE on existing ROW, InnoDB keeps on growing.
Assuming I have 100 Rows, each Storing 500KB of information, My InnoDB size is 10MB, now when I call UPDATE on all rows (no insert/ no delete), the innodb grows by ~8MB for every run I do. All I am doing is I am storing exactly 500KB of data in each row, with little modification, and size of blob is fixed.
What can I do to prevent this?
I know about optimize table, but I cant do it because on regular usage, the table is going to be 60-100GB big, and running optimize will just stall entire server.
I am currently designing a forum as a personal project. One of the recurring issues I've come across is database queries in loops. I've managed to avoid doing that so far by using table joins or caching of data in arrays for later use.
Right now though I've come across a situation where I'm not sure how I can write the code in such a way that I can use either of those methods easily. However I'd still prefer to do at most 2 queries for this operation rather than 1 + 1 per group of forums, which so far has resulted in 5 per page. So while 5 isn't a huge number (though it will increase for each forum group I add) it's the principle that's important to me here, I do NOT want to write queries in loops
What I'm doing is displaying forum index groupings (eg admin forums, user forums etc) and then each forum within that group on a single page index, it's the combination of both in one page that's causing me issue. If it had just been a single group per page, I'd use a table join and problem solved. But if I use a table join here, although I can potentially get all the data I need it'll be in one mass of results and it needs displaying properly.
Here's the code (I've removed some of the html for clarity)
<?php
$sql= "select * from forum_groups"; //query 1
$result1 = $database->query($sql);
while($group = mysql_fetch_assoc($result1)) //first loop
{?>
<table class="threads">
<tr>
<td class="forumgroupheader"> <?php echo $group['group_name']; ?> </td>
</tr>
<tr>
<td class="forumgroupheader2"> <?php echo $group['group_desc']; ?> </td>
</tr>
</table>
<table>
<tr>
<th class="thforum"> Forum Name</th>
<th class="thforum"> Forum Decsription</th>
<th class="thforum"> Last Post </th>
<tr>
<?php
$group_id = $group['id'];
$sql = "SELECT forums.id, forums.forum_group_id, forums.forum_name, forums.forum_desc, forums.visible_rank, forums.locked, forums.lock_rank, forums.topics, forums.posts, forums.last_post, forums.last_post_id, users.username
FROM forums
LEFT JOIN users on forums.last_post_id=users.id
WHERE forum_group_id='{$group_id}'";
//query 2
$result2 = $database->query($sql);
while($forum = mysql_fetch_assoc($result2))
//second loop
{?>
So how can I either
a) write the SQL in such a way as to remove the second query from inside the loop or
b) combine the results in an array
either way I need to be able to access the data as an when so I can format it properly for the page output, ie within the loops still.
Hi
In my mode I am selecting a field as
$query1 = $this->db->query("SELECT dPassword
FROM tbl_login
WHERE dEmailID='[email protected]'");
How to return dpassword as a variable to my controller
I tried this way return dpassword;
Hi guys, I have a slight problem, I am trying to capture the input of two buttons, one yes, one no, into a database but for some reason the database doesn't always show the value of the button clicked, it just shows up blank.
<form action="refer.php" method="post" id="formID" >
<div class="prompt_container" style="float: left;">
<span class="prompt_item"><input type="image" src="images/yes.jpg" alt="submit" value="yes" onclick="this.disabled=true,this.form.submit();" /></span>
<input type="hidden" name="refer" value="yes">
</div>
</form>
<form action="thank_you.php" method="post" id="formID" >
<div class="prompt_container" style="float: right;">
<span class="prompt_item"><input type="image" src="images/no.jpg" alt="submit" value="no" onclick="this.disabled=true,this.form.submit();" /></span>
<input type="hidden" name="refer" value="no" >
</div>
</form>
Apparently anything lower than IE8 will ignore the value attribute of all form inputs.
How could I get this to work properly in all browsers? jQuery or Javascript maybe?
Hi I have three tables and I want to know how much jobs with the wage of 1000 an employee has had
The first SQL query gives me the names of all the employees that has recieved 1000 for a job
SELECT distinct first_name
FROM employee, job, link
WHERE job.wage = 1000
AND job.job_id = link.job_id and employee.employee_id = link.employee_id;
The second SQL query gives me the total number for all employees of how much jobs they have made for 1000
SELECT count(wage)
FROM employee, job, link
WHERE job.wage = 1000
AND job.job_id = link.job_id and employee.employee_id = link.employee_id;
I was wondering if there was a way of joining both queries and also making the second for each specific employee???
Consider the following table daterange
_date trading_day
------------------------
2011-08-01 1
2011-07-31 0
2011-07-30 0
2011-07-29 1
2011-07-28 1
2011-07-27 1
2011-07-26 1
2011-07-25 1
2011-07-24 0
2011-07-23 0
2011-07-22 1
2011-07-21 1
2011-07-20 1
2011-07-19 1
2011-07-18 1
2011-07-17 0
I'm in need of a query that returns a _date, x days before a given _date. When counting back, _days with trading_day = 0 should be ignored. A few examples:
input | output
-------------------------+------------
1 day before 2011-07-19 | 2011-07-18
2 days before 2011-08-01 | 2011-07-28 (trading_day = 0 don't count)
3 days before 2011-07-29 | 2001-07-26
The first one is easy:
SELECT _date
FROM daterange
WHERE trading_day = 0 AND _date < '2011-07-19' LIMIT 1
But I don't know how to query for the other examples. Do you?
Suppose I have my models set up already.
class books(models.Model):
title = models.CharField...
ISBN = models.Integer...
What if I want to add this column to my table?
user = models.ForeignKey(User, unique=True)
How would I write the raw SQL in my database so that this column works?
I am trying to add the SQL_CALC_FOUND_ROWS into a query (Please note this isn't for pagination)
please note I am trying to add this to a cakePHP query the code I currently have is below:
return $this->find('all', array(
'conditions' => $conditions,
'fields'=>array('SQL_CALC_FOUND_ROWS','Category.*','COUNT(`Entity`.`id`) as `entity_count`'),
'joins' => array('LEFT JOIN `entities` AS Entity ON `Entity`.`category_id` = `Category`.`id`'),
'group' => '`Category`.`id`',
'order' => $sort,
'limit'=>$params['limit'],
'offset'=>$params['start'],
'contain' => array('Domain' => array('fields' => array('title')))
));
Note the 'fields'=>array('SQL_CALC_FOUND_ROWS',' this obviously doesn't work as It tries to apply the SQL_CALC_FOUND_ROWS to the table e.g. SELECTCategory.SQL_CALC_FOUND_ROWS,
Is there anyway of doing this? Any help would be greatly appreciated, thanks.
I have 4 tables: tempTBL, linksTBL and categoryTBL, extra
on my tempTBL I have: ID, name, url, cat, isinserted columns
on my linksTBL I have: ID, name, alias columns
on my categoryTBL I have: cl_id, link_id,cat_id
on my extraTBL I have: id, link_id, value
How do I do a single query to select from tempTBL all items where isinsrted = 0 then insert them to linksTBL and for each record inserted, pickup ID (which is primary) and then insert that ID to categoryTBL with cat_id = 88. after that insert extraTBL ID for link_id and url for value.
I know this is so confusing, put I'll post this anyhow...
This is what I have so far:
INSERT IGNORE INTO linksTBL (link_id,link_name,alias)
VALUES(NULL,'tex2','hello'); # generate ID by inserting NULL
INSERT INTO categoryTBL (link_id,cat_id)
VALUES(LAST_INSERT_ID(),'88'); # use ID in second table
I would like to add here somewhere that it only selects items where isinserted = 0 and iserts those records, and onse inserted, will change isinserted to 1, so when next time it runs, it will not add them again.
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
I have a Select Left Join Query whis displays me the rows for the latest changedone(its a time) column name ("field" should not be equal) column name ("trackid" should not be equal), and column name "Operation should be "UPDATE" ", below is the query I am talking about...
SELECT j1. *
FROM jos_audittrail j1
LEFT OUTER JOIN jos_audittrail j2 ON ( j1.trackid != j2.trackid
AND j1.field != j2.field
AND j1.changedone < j2.changedone )
WHERE j1.operation = 'UPDATE'
AND j2.id IS NULL
Now here I don't want a row to be displayed with a two particular column's value i.e. "field's value" the value is "LastvisitDate" and "hits"
Now if if append the condition in the above query that
" AND j1.field != 'lastvistDate' AND j1.field != 'hits' "
theni do not get any result...
The table structure is
jos_audittrail:
id
trackid
operation
oldvalue
newvalue
table_name
live
changedone(its a time)
I hope i have given the details properly If u still find something missing I will try to provide it more better way...
Pls help me to avoid those two rows with those to mentioned value of "field"
I have a column that contains links. The problem is that the titles of the links are in the same column, so it looks like this:
linktitle|-|linkurl
I want link title and linkurl in separate columns.
I've created a new column for the urls, so I'm looking for a way to extract them and update the linkurl column with them. Is there any clever way to construct a query that does this?
$sql = "SELECT # FROM users WHERE onduty = 1 AND loc_id = '{$site}';";
$result = mysql_query($sql);
I simply want to test if this is true or false. If it returns 0 rows, I want next line to be something like:
if (!$result) { //do this; }
However, in my test, I am getting false when I know it should be true. Is this sound logic here?
(note, yes I know I should be using mysqli_query, that is not what I am asking here)
This sounds kinda dumb, but I have a sharded DB that I no longer think I need to run on 2 machines, and would like to run on one single machine instead.
Any ideas on how that can potentially be done? There are lots of resources on how i can achieve the converse, but very little on how this can be done
Hey guys I am trying to return the latest 10 results of a query from a php file, through json format, to a jquery getjson function that prints results. I am getting weird problems though. For example I am only getting 8 entries returned, and some are disordered, and sometimes nothing is returned. I am not really sure what I am doing wrong, so if anyone has any ideas I would really appreciate it.
This is my query ($res)
SELECT time, user, message FROM comments WHERE topic_id='$topic_id' ORDER BY time DESC LIMIT 10
This is the processing of the results
while($row = mysql_fetch_array($res)){
$message=$row['message'];
$user=$row['user'];
if($row['message'] AND $row['time'] > $_GET['time'])
$data[] = $row;
}
$out = json_encode($data);
print $out;
And this is the retrieval where prepare is just a function that returns information into a div
$.getJSON(files+"processing.php?action=load&time="+0+"&topic_id="+topic_id+"&t=" + (new Date()), function(json) {
if(json.length) {
for(i=0; i < 10; i++) {
$('#comment-list').prepend(prepare(json[i]));
$('#list-' + count).fadeIn(1500);
}
}
});
function prepare(response) {
count++;
var string = '<li class="comment-list" id="list-'+count+'">'
//organize info into a div
+'</li>';
return string;
}
I want to use mode rewrite to display the following:
mydomain.com/Florida/Tampa/ instead of mydomain.com/place.php?state=Florida&city=Tampa
I've akready done this: (since I think it might make a difference!)
mydomain.com/[name].html instead of mydomain.com/profile?user=[name]
Here is the code!
Options +FollowSymLinks
Options +Indexes
RewriteEngine On
RewriteBase /
RewriteCond %{SCRIPT_FILENAME}! !-f
RewriteCond %{SCRIPT_FILENAME}! !-d
RewriteRule (.*).html profile.php?user=$1 [QSA.L]
I have a table with products, their amount and their price. I need to select all entries where the average price per article is between a range.
My query so far:
SELECT productid,AVG(SUM(price)/SUM(amount)) AS avg FROM stock WHERE avg=$from AND avg<=$to GROUP BY productid
If do this, it tells me avg doesnt exist.
Also i obviously need to group by because the sum and average need to be per wine