Jump to content

Search the Community

Showing results for tags 'query'.

More search options

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


  • Welcome to PHP Freaks
    • Announcements
    • Introductions
  • PHP Coding
    • PHP Coding Help
    • Regex Help
    • Third Party Scripts
    • FAQ/Code Snippet Repository
  • SQL / Database
    • MySQL Help
    • PostgreSQL
    • Microsoft SQL - MSSQL
    • Other RDBMS and SQL dialects
  • Client Side
    • HTML Help
    • CSS Help
    • Javascript Help
    • Other
  • Applications and Frameworks
    • Applications
    • Frameworks
    • Other Libraries
  • Web Server Administration
    • PHP Installation and Configuration
    • Linux
    • Apache HTTP Server
    • Microsoft IIS
    • Other Web Server Software
  • Other
    • Application Design
    • Other Programming Languages
    • Editor Help (Dreamweaver, Zend, etc)
    • Website Critique
    • Beta Test Your Stuff!
  • Freelance, Contracts, Employment, etc.
    • Services Offered
    • Job Offerings
  • General Discussion
    • PHPFreaks.com Website Feedback
    • Miscellaneous

Find results in...

Find results that contain...

Date Created

  • Start


Last Updated

  • Start


Filter by number of...


  • Start





Website URL








Donation Link

Found 35 results

  1. Is it possible to do this with one query? Tried with union and join but no luck. <?php $query = 'SELECT cashReward, pointsReward FROM pts WHERE signupsAvailable > 0 AND status = "active" AND id = :id'; $select = $db->prepare($query); $select->bindParam(':id', $id, PDO::PARAM_INT); $select->execute(); $rowCount = $select->rowCount(); $queryC = 'SELECT COUNT(id) FROM ignored_pts WHERE user = :username AND ptsId = :id'; $selectC = $db->prepare($queryC); $selectC->bindParam(':username', $userInfo['username'], PDO::PARAM_INT); $selectC->bindParam(':id', $id, PDO::PARAM_INT); $selectC->execute(); $count = $selectC->fetch(PDO::FETCH_COLUMN); if($rowCount == 1){// PTS // $row = $select->fetch(PDO::FETCH_ASSOC); if($count == 0){// IGNORED PTS // // ...................... // // INSERT INTO ignored_pts TABLE $row['cashReward'], $row['pointsReward']// // ...................... // print 'PTS IGNORED'; }else{ print 'You have already ignored this PTS!'; } }else{ print 'An invalid PTS was provided!'; } $db = NULL; ?>
  2. Hi everyone, I'm having troubles with query from database. I want to select all from one table and select sum from second table in the same time. Is this possible? Here is my script: $conn = mysqli_connect('localhost', 'root', '', 'test'); if (!mysqli_set_charset($conn, "utf8")) { printf("Error loading character set utf8: %s\n", mysqli_error($conn)); } $sql = "SELECT first_id, first_name FROM first"; $sql = "SELECT sum(total) as SumTotal FROM second"; $result = mysqli_query($conn, $sql); while ($data = mysqli_fetch_array($result)) { echo '<tr>'; echo '<th>'.$data['first_id'].'</th>'; echo '<th>'.$data['first_name'].'</th>'; echo '<th>'.$data['SumTotal'].'</th>'; echo '</tr>'; } mysqli_close($conn); Thanks!
  3. I have a question about how I would structure a query. I have 2 tables. Store store_id -- primary key zip_code Merchandise item_id -- primary key item_name description cost storeID -- foreign key that references the store_id from the store. I am using InnoDB. I want the user to be able to input 1-5 choices that are 1 word apiece and it would cross reference the decription and tell me the store that has them both. For instance, choice 1 = clock, choice 2 = paper, and choice 3 = food. It would query a result that would give me the stores that have items that match the description of them all. For instance, it would return Wal - Mart based on this query because it would be the only store that had an item to match each description. Any help is much appreciated. Thanks!
  4. Hi all, I have this simple query $query = 'SELECT count(ID), col1, col2, col3 FROM table WHERE email = ?'; // I know I have used capitals for ID $stmt = $link->prepare($query); $stmt->bind_param('s',$email); which works great in xampp but gives a FATAL ERROR when I run it on a server. Uncaught Error: Call to a member function bind_param() on boolean in /var/www/html/ all the column names, tables names etc. in the query are correct but the query seems to fail so prepare must be returning a false value. Hence the error. Anyone has any idea what's going on? Thanks loads !
  5. So i made a login page, but whenever i enter right, wrong, or no password at all, it always displays wrong password. I've been trying to fix it for hours, but I just can't seem to find the error. It's like it's skipping if the password is right statement and goes straight through the else statment. <?php $connection = mysql_connect("com-db-02.student-cit.local","***","***") or die (mysql_error()); if ($connection) echo "Connection successful"; else echo "Connection failed"; $db = mysql_select_db("TEAM20") or die (mysql_error()); ?> <?php $_SESSION['customeremail'] = $_POST['user']; $_SESSION['password'] = $_POST['password']; function signIn() { session_start(); if(!empty($_POST['user'])) { $query = mysql_query("SELECT * FROM customer where customeremail = '$_POST[user]' AND password = '$_POST[password]'"); $row = mysql_fetch_array($query); if(!empty($row['customeremail']) AND !empty($row['password'])) { $_SESSION['customeremail'] = $row['customeremail']; getCustDetails(); echo "Successfully login to user profile page.."; echo "<a href=userlogin.php>Profile</a>"; } else { echo "Sorry... YOU ENTERED WRONG ID AND PASSWORD"; echo "<a href=login.html>Try Again</a>"; } } } function getCustDetails() { $queryId = mysql_query("SELECT customerID, firstname FROM Customer WHERE customeremail = '$_POST[user]'"); while($rowId = mysql_fetch_array($queryId)) { $_SESSION['customerID'] = $rowId['customerID']; $_SESSION['firstname'] = $rowId['firstname']; } echo "Code: ".$_SESSION['customerID']; echo "Name: ".$_SESSION['firstname']; } if(isset($_POST['submit'])) { signIn(); } ?>
  6. Hi look for some help with querying based on todays date. I'm trying to count all the entries in the database based on username, status, date. The date bit is where I' stuck (see screen shot of database) Based on data in database I should get a count of 3 but get nothing?? The result I get is 0 and should be 3 based on date Any help would be a great help Cheers Chris //date bit $d=strtotime("today"); $wholedate2 = date("Y-m-d", $d); //query $query_rsUser = "SELECT COUNT(*) FROM quotes WHERE quotes.quote_user = 'username' AND quotes.quote_complete = '$date2' AND quotes.quote_status = 'Complete'";
  7. Here's what I am trying to do. Retrieve "title" and "slug title" from all the rows in a table. Update them with new "title", "slug title" and "brand name". Here's my query. It updates the table with the brand names for each row. But for some reason, it removes the title and slug title from all the rows in the table. Is there a reason why it's doing that? function getIt($var) { $pos = strpos($var, ' - '); echo(substr($var, $pos+3)."\n"); } if(isset($_POST['submit'])) { $get_record = $db->prepare("SELECT * FROM items"); $get_record->execute(); $result_record = $get_record->fetchAll(PDO::FETCH_ASSOC); if(count($result_record) > 0){ foreach($result_record as $row) { $get_item_id = intval($row['item_id']); $get_item_title = trim($row['item_title']); $get_item_title_slug = trim($row['item_title_slug']); $new_title = getIt($get_item_title); $new_title_slug = Output::createSlug($new_title); $brand_name = substr($get_item_title, 0, strpos($get_item_title, ' - ')); $update_record = $db->prepare("UPDATE items SET brand_name = :brand_name, item_title = :item_title, item_title_slug = :item_title_slug WHERE item_id = :item_id"); $update_record->bindParam(':item_id', $get_item_id); $update_record->bindParam(':brand_name', $brand_name); $update_record->bindParam(':item_title', $new_title); $update_record->bindParam(':item_title_slug', $new_title_slug); if(!$update_record->execute()) { $errors[] = 'There was a problem updating the item.'; } } } else { $errors[] = 'No item found.'; } }
  8. HI. I have written a mysql database query to add up invoice totals. It seems to be automatilaly adding .01 to the result when I run a group by. Let me explain more .. When I do a select on the field that I am adding up the result is $132.25 - the query is as follows select ttl_invoice_due from 65_it where st_id = 96011; When I run the following query that uses sum and group by - the $132.25 becomes $132.26 - the sum query is SELECT 65_st.job_compid, 65_client.uid_client, 65_client.comp_name, Sum(65_it.ttl_invoice_due) AS SumOftotal_due, Sum(65_it.ttl_invoice) AS SumOftotal_invoice, Sum(65_it.ttl_invoice_payments) AS SumOftotal_amnt_paid FROM 65_client INNER JOIN (65_st INNER JOIN 65_it ON 65_st.st_id = 65_it.st_id) ON 65_client.uid_client = 65_st.uid_client WHERE 65_st.uid_client = 1905 and 65_st.job_compid = 1 and 65_st.sales_date<=curdate() GROUP BY 65_st.job_compid, 65_client.uid_client HAVING (((Sum(65_it.ttl_invoice_due))<>0)) order by 65_client.comp_name; The 65_it.ttl_invoice_due field is defined in the table as Double(12,2) I dont know why the sum is returing $132.26 where it should be $132.25 as in the simple query ?.
  9. I'm currently using a external module from an opencart vendor on my opencart store, however I keep getting a error stating mysql has gone away. Thinking this might be an issue with the mysql wait time setting I contacted my server support team and asked them to investigate, below is there answer. I'm struggling to understand exactly why the query is so badly designed as stated below, any advice on the subject would be greatly appreciated
  10. firstly, I am not a db programmer. I ply my trade in WordPress stuff, but not in-depth db structuring from scratch. please keep that in mind as I do my best to ask this question? I have made some headway creating two tables that I think will almost, kinda do what I want, which is: Phillips pid1Barnes pid2Moore pid3de Mohrenschildt pid4Oswald pid5 Hunt pid6Sturgis pid7Moore pid3 I've created a (really large) outline in html that simply shows, in effect: Phillips (knows) » Barnes (supervised) » Moore (supervised) » de Mohrenschildt (knows) » Oswald ... AS WELL AS, Phillips (knows) » Hunt (knows) » Sturgis (followed, who also knows) » Moore (same, pid3) ... I put Sturgis followed by Moore specifically to show that these relationships are in no way numerically sequential from the ASSOCS table. There will be 4 or 500 Persons, (and 40 or 50 Organizations, once I get this solved). You can see an example of it here (there's lots of data going on in this outline): http://stemmonsfreeway.com/military-industrial-intelligence-anti-castro-syndicated So, I've come up with these tables: [persons] id, name ----------------- 1 Phillips 2 Barnes 3 Moore 4 de Mohrenschildt 5 Oswald 6 Hunt 7 Sturgis [assocs] p_id, a_id ---------------- 1 2 2 3 3 4 4 5 1 6 6 7 7 3 And I have this query: SELECT a1.p_id, p1.name AS 'Name', a2.p_id, p2.name AS 'FName1', a3.p_id, p3.name AS 'FName2' FROM assocs a1 JOIN assocs a2 ON a1.p_id = a2.a_id JOIN assocs a3 ON a2.p_id = a3.a_id JOIN persons p1 ON a1.p_id = p1.id JOIN persons p2 ON a2.p_id = p2.id JOIN persons p3 ON a3.p_id = p3.id WHERE p1.id = 1 AND p2.id = 2 AND p3.id = 3;which returns: 1 Phillips 2 Barnes 3 Moore *** The problem I have is where a trail ends, Phillips to Oswald, and starts again, Phillips to Hunt to Moore, for instance. *** What I need is a way to define an end to a string of associations and a start of the next one, perhaps with another field or two in the ASSOCS table, or another table... (I'd also love to be able to denote one of a few types of relationships, i.e. "friend" "foe" "supervised" "worked for" ...) *** I'm hoping some kind soul can help me with a query that can do this, and some advice on how to handle it in the tables I've started with...?
  11. Here's what I am trying to do. Users Table user_id, sponsor_id, username, filled_positions, position_1, position_2, position_3, position_4, position_5 1 0 user 1 4 user 2 user 3 user 4 user 5 2 1 user 2 2 user 4 user 5 3 1 user 3 4 2 user 4 5 2 user 5 Above is a "Users" table. Here's what I am trying to do. Insert new users into the table. Say I already have the users table set up with 5 users. I want to add User 6. I want to loop through the users in the table and find the next empty position and update it with the new user id. In this scenario diagram above, the next empty position is Row 1 - position_5. The one after that is Row 2 - position_3 and then Row 2 - position_4...etc. It basically loops through rows and checks each position. So User 6 will be placed under Row 1 - position_5 and User 7 will be placed under Row 2 - position_3. How can one go on about doing that?
  12. Hello. I'm a newbie so sorry if this isn't the best forum to post my problem. I am using a MySQL and PHP to create a web app. I have authentication, and I can register users. I also have a form that users provide information and it is successfully inserting data into a table in my database. I will use fictional fields for my database table called meal_info: username dateStartedDiet numberMealsPerDay costPerMeal Problem: Select user-specific data from the MySQL database, using Session username to select only the current user's data, then display it and do some calculations. Here is thecode at the top, and I am fairly sure it's working: session_start(); //execute commone code require("common.php"); //includes code to connect to database, etc. if(empty($_SESSION['user'])) { // If they are not, we redirect them to the login page. header("Location: login.php"); // Remember that this die statement is absolutely critical. Without it, // people can view your members-only content without logging in. die("Redirecting to login.php"); } Here is the part of the code that has to do with displaying user data: $userID = $_SESSION['user']['username']; //create a variable that is the session username which is identical to the field in our MySQL table $query = "SELECT * FROM meal_info WHERE username = $userID"; //our SELECT statement $result = db->query($query); //execute the query $row_count = $result->num_rows;//count the rows in the table and place in variable to use in incremental loop code for ($i = 0; $i < $row_count; $i++) : $row = $result->fetch_assoc(); //for each row in the table, fetch and create and array $dateStart = $row['dateStartedDiet']; $numberMeals = $row['numberMealsPerDay']; $costMeal = $row['costPerMeal']; echo $dateStart; echo $numberMeals; echo $costMeal;
  13. SELECT user_id FROM users WHERE active = :active ORDER BY RAND() LIMIT 1 The above query works. But what if the users table is filled with thousands of users? Will this query break or be very slow? If so, what's the alternative solution to this?
  14. This is what I am trying to accomplish. 1. I have two tables. T1: Users and T2: Earnings. 2. I want to find a single random user whose deposit amount is greater than the listed amount. Below is my foreach loop within foreach loop trying to do the above. I was wondering if there is a more simple way to do this task? Is there a way to combine these two queries together? $listed_amount = 1000; // find a random user $find_user = $db->prepare("SELECT user_id FROM users WHERE user = :user AND active = :active ORDER BY RAND() LIMIT 1"); $find_user->bindValue(':user', 1); $find_user->bindValue(':active', 1); $find_user->execute(); $result_user = $find_user->fetchAll(PDO::FETCH_ASSOC); if(count($result_user) > 0) { foreach($result_user as $row) { $user_id = $row['user_id']; // find that random user's deposit amount $get_deposits = $db->prepare("SELECT deposit FROM earnings WHERE sent_to = :sent_to AND status = :status"); $get_deposits->bindParam(':sent_to', $user_id); $get_deposits->bindValue(':status', 1); $get_deposits->execute(); $result_deposits = $get_deposits->fetchAll(PDO::FETCH_ASSOC); if(count($result_deposits) > 0) { $ub = 0; foreach($result_deposits as $key=>$row) { $ub+= $row['deposit']; } $total_deposits = $ub; } if($total_deposits > $listed_amount) { // show the user } else { // hide the user } } }
  15. This should all be working but I think I might be overlooking something. Problem 1. I am using ajax to process a form. I don't think the problem is with ajax(works with other queries), but rather with php. Here is my simple query. $stmt = $db->prepare("SELECT * FROM records WHERE request_by = :request_by, request_to = :request_to AND session = :session"); $stmt->bindParam(':request_by', $byUserid); $stmt->bindParam(':request_to', $toUserid); $stmt->bindValue(':session', 1); $stmt->execute(); $result = $stmt->fetchAll(PDO::FETCH_ASSOC); if(count($result) > 0) { $success = 'This was successful.'; } else { $error = 'There was a problem.'; } This above query gives me this error. Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' request_to = '7' AND session = '1'' at line 1 Problem 2. Say the main page is "index.php" with submit form and I am using ajax via url: process.php. Well on "index.php" page, I have foreach loop that gets me a "$toUserid" for each record. I would like to use that "$toUserid" in "process.php" page. I tried setting it as a session but still it won't give me that exact userid. It'll give me the same userid as the user I am currently logged in. What exactly I am doing wrong and how I can i fix it?
  16. Hi, I have a table with the following data: id name hours annual_leave ================================= 1 Chris 10 0 2 Tony 15 0 3 Mark 9 0 4 John 23 0 5 Lee 8 0 What i want to achieve is: If an employee worked 10 hours or more then reward him/her with 1 day annual leave. By default the value of annual_leave is "0". So i find all eligible employees and store their IDs in a table: $eligible = array ( 0 => 1, 1=>2 , 2=>4); id name hours annual_leave ================================= 1 Chris 10 1 2 Tony 15 1 3 Mark 9 0 4 John 23 1 5 Lee 8 0 What i want to do is to create a single UPDATE query that will find all 3 employees and change the annual_leave value from 0 to 1. I can do this easily within the loop i use to create the $eligible table.. But i have the following questions: a) can i use a single UPDATE query to update multiple rows/columns in a table? b) what is better? a single query to update multiple rows/columns or multiple queries that update a single row/column per time c) can i combine PHP and MYSQL to syntax a query? because in case the $eligible array carries 1000 entries i will need to iterate through it using a FOR or WHILE loop.. Any other comments will be appreciated..
  17. So I have a simple query that adds a text record into a MySQL database table. It works great with exception of one thing. I noticed that if I have "&" symbol in my paragraph, the text after that symbol won't be inserted into the table. The text before that symbol will insert fine. It seems to be doing that only with & symbol; other symbols insert and show up fine. can anyone tell me why this is happening?
  18. I have a pdo prepared statement that fetches records from mysql database. The records show up on the page. However if there are no records on a page, I get this error message. "QLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-10' at line 4" Here is my statement $getCategoryId = $_GET['id']; $limit = 10; $offset = ($page - 1) * $limit; $statement = $db->prepare("SELECT records.*, categories.* FROM records LEFT JOIN categories ON records.category_id = categories.category_id WHERE records.category_id = :category_id ORDER BY record_id DESC LIMIT {$limit} OFFSET ".$offset); $statement->bindParam('category_id', $getCategoryId); $statement->execute(); $results = $statement->fetchAll(PDO::FETCH_ASSOC); If I remove try and catch block, it'll tell me exactly which line is giving the issue. So from the above code, the error has to do with "$statement->execute();". This is where the error occurs. As far as I know, the above pdo statement is correct. Can you tell me if something is wrong with it?
  19. Hello I have a database with matches for Championship and want to display a list of upcoming matches and average number of goals in matches for each of the teams from last 5 matches that they were involved in. E.g. SELECT ko_time, home, away FROM matches_table WHERE ko_time = TODAY will give me something like this. 15:00 Blackburn Rovers Charlton Athletic 15:00 Brentford Preston North End 15:00 Bristol City Reading 15:00 Huddersfield Town Bolton Wanderers 15:00 Hull City Queens Park Rangers Now, I know how to calculate an average number of goals for one team from their last 5 matches e.g. SELECT AVG(home_score + away_score) FROM matches_table WHERE home = "Brentford" or away = "Brentford" ORDER BY match_date DESC LIMIT 5 This will give me average number of goals from 5 last matches of Brentford. Now, I'm not sure how to combine these two queries and have the averages calculated for all teams and displayed like below: 15:00 Blackburn Rovers Charlton Athletic 2.3 2.4 15:00 Brentford Preston North End 1.4 2.3 15:00 Bristol City Reading 2.6 0.9 15:00 Huddersfield Town Bolton Wanderers 3.6 1 15:00 Hull City Queens Park Rangers 0.8 1.65 The table contains all matches - played and fixtures, which are updated once finished. Has anyone got any ideas? Thank you Attached link to db Database
  20. Good evening, I am working on a query at my office that is joining multiple tables in order to display a User notification (similar to facebook). The notifications are based on user department, access level, and position. I want to display all of the announcements filtered for the user that is in the CWAnnouncement table and the NotificationArchive Table where the NotificationArchive.Active is equal to 1 or if they have not made an entry in the NotificationArchive table. If NotificationArchive.Active is equal to 0 then I do not want it to show up in the query results. So far the user filter works but I am having trouble display the results based on who the user is and whether or not they have an announcement in the NotificationArchive table. I hope this make sense. Help would be greatly appreciated this will be my first major project for this company and I want to do a good job for them. Thanks, SELECT ROW_NUMBER() OVER(ORDER BY StartDate DESC) AS 'Rownumber', dbo."User".USERID, CWNotifications.* FROM dbo."User" LEFT JOIN ( SELECT dbo.CWAnnouncements.AnnouncementID, dbo.CWAnnouncements.Title, dbo.CWAnnouncements.Message, dbo.CWAnnouncements.StartDate, dbo.CWAnnouncements.EndDate, dbo.CWAnnouncements.AllStaff, dbo.CWAnnouncements.MGR, dbo.CWAnnouncements.L504, dbo.CWAnnouncements.AdminSupport, dbo.CWAnnouncements.EXP, dbo.CWAnnouncements.IT, dbo.CWAnnouncements.Legal, dbo.CWAnnouncements.PSA, dbo.CWAnnouncements.SRV, dbo.CWAnnouncements.QC, dbo.CWAnnouncements.Active, dbo.CWAnnouncements.UserID, dbo.CWAnnouncements.LS, dbo.CWAnnouncements.LSA, dbo.CWAnnouncements.FileRoom, dbo.CWAnnouncements.Collateral, NotificationArchive.NotificationID, NotificationArchive.Active AS ActiveNote FROM dbo.CWAnnouncements Left JOIN dbo.NotificationArchive ON dbo.CWAnnouncements.AnnouncementID=dbo.NotificationArchive.AnnouncementID WHERE (dbo.NotificationArchive.UserID='#GetAuthUser()#' AND dbo.CWAnnouncements.Active=1 AND dbo.NotificationArchive.UserID IS NULL) OR dbo.CWAnnouncements.UserID='#GetAuthUser()#' AND dbo.NotificationArchive.UserID='#GetAuthUser()#' AND dbo.CWAnnouncements.Active=1 AND dbo.NotificationArchive.Active = 1 ) CWNotifications ON (dbo."User".MGR>=CWNotifications.MGR AND CWNotifications.MGR >=1 OR dbo."User".QC>=CWNotifications.QC AND CWNotifications.QC >=1 OR dbo."User".IT>=CWNotifications.IT AND CWNotifications.IT >=1 OR Dbo."User".LEG>=CWNotifications.Legal AND CWNotifications.Legal >1 OR (dbo."User".AdminSupport=CWNotifications.AdminSupport AND CWNotifications.AdminSupport = 1 OR dbo."User".Fileroom=CWNotifications.Fileroom AND CWNotifications.Fileroom = 1 OR dbo."User".Collateral=CWNotifications.Collateral AND CWNotifications.Collateral = 1) OR (dbo."User".L504>=CWNotifications.L504 AND CWNotifications.L504 >= 1 or dbo."User".EXP>=CWNotifications.EXP AND CWNotifications.EXP >= 1 or dbo."User".SRV>=CWNotifications.SRV AND CWNotifications.SRV >= 1 or dbo."User".PSA>=CWNotifications.PSA AND CWNotifications.PSA >= 1) AND (dbo."User".LS=CWNotifications.LS AND CWNotifications.LS = 1 OR dbo."User".LSA=CWNotifications.LSA AND CWNotifications.LSA = 1) OR CWNotifications.AllStaff=1) AND CWNotifications.Active=1 WHERE (dbo."user".USERID='#GetAuthUser()#') ORDER BY StartDate DESC
  21. I have a small database snippet over at SQLFiddle: http://sqlfiddle.com/#!2/9ff722/55 I need help with writing a query that will allow me to track students in a certain cohort from beginning to graduation. I have two tables: application and stu_acad_cred. The application has the start term and the stu_acad_cred are course registrations. If I want to track students in a fall 2012 (12/FA) cohort, then the query must join the two tables; if the student has a start term of 12/FA *and* has registered for 12/FA courses, the student should be counted. I then want to track those student from year to year or term to term to see if the number of students that started in the 12/FA cohort actually decrease overtime. So, the results should return something similar to below. | # Students (12/FA Cohort) | Year | ------------------------------------- | 2 | 2012 | | 2 | 2013 | | 2 | 2014 | I am not sure if this can be done straight forward or with a stored procedure but any help or direction is greatly appreciated.
  22. Hi Mate, i need help with my mysql query.. SELECT * FROM `schedules` WHERE `clientID`=''; it doesn't select any values having an empty clientID... there are rows on my database that i need to select that has an empty clientID and using this statement does not return any value... but if i use `clientID`!='' it works fine, it returns all the data having clientID. Any help is highly appreciated. Thank you in advance. Neil
  23. Hi there. I got a table named `area` that has a POLYGON field. There are some rows with specified area in table. Now i want to check if a point ("59.5594597, 36.3556769" for example) is within the polygons or not. searched a lot and none works. my polygon has 102 points in case of need. Thanks.
  24. So I have two tables. Table 1 - Records Table 2 - Earnings I basically want to retrieve 6 active records from highest to lowest earnings. Here are the table setups. Records Table record_id | record_name | status 1 record_1 1 2 record_2 0 3 record_3 1 4 record_4 1 5 record_5 1 6 record_6 1 7 record_7 1 8 record_8 1 -------------------------------------------- Earnings Table earning_id | record_id | amount 1 1 $100 2 2 $200 3 3 $300 4 4 $400 5 5 $500 6 6 $600 7 7 $700 8 8 $800 9 1 $100 10 1 $100 As you can see I have total of 8 records. Only 7 of them are active. And record_1 has multiple earning rows. This is the tricky part. Normally I can retrieve the records seperatly and the earnings seperatly but I would like to know how can I combine this into a single query to achieve the same result so that I can list 6 active records from highest to lowest earnings? Here is my way so far. $find_records = $db->prepare("SELECT record_id, record_name, status FROM records WHERE status = :status"); $find_records->bindValue(':status', 1); $find_records->execute(); $result_find_records = $find_records->fetchAll(PDO::FETCH_ASSOC); if(count($result_find_records) > 0) { foreach($result_find_records as $row) { $record_id = $row['record_id']; $record_name = $row['record_name']; $record_status = $row['record_status']; $get_earnings = $db->prepare("SELECT amount FROM earnings WHERE record_id = :record_id"); $get_earnings->bindParam(':record_id', $record_id); $get_earnings->execute(); $result_earnings = $get_earnings->fetchAll(PDO::FETCH_ASSOC); if(count($result_earnings) > 0) { $ub = 0; foreach($result_earnings as $key=>$row) { $ub+= $row['deposit']; } $record_amount = $ub; } } }
  25. I have users who signed up for an email list and there is a table of them and the join date is in unix timestamp. I do not care about the date, only the time they signed up. I am putting together code to be run on cron to decide who gets emailed. It will be run probably every hour. When it runs the first thing I need it to do is select all users who from the current time through the next hour registered. This way I can then email people around the time they initially joined my site. Here is what I have: //$input is the minutes range (which will be most likely an hour so this variable should be 60) function to_email($input) { require_once('dbconnect.php'); $cur_hours = date('H'); $cur_minutes = date('i'); $cur_seconds = ($cur_minutes + ($cur_hours * 60)) * 60; $end_seconds = $input * 60; $end_seconds = $cur_seconds + $end_seconds; $result = mysql_query("SELECT * FROM usertable WHERE " . $cur_seconds . " <= (HOUR(opt_time)*60*60) + (MINUTE(opt_time)*60) + SECOND(opt_time) AND '" . $end_seconds . " > (HOUR(opt_time)*60*60) + (MINUTE(opt_time)*60) + SECOND(opt_time)'"); $row = mysql_fetch_array($result); } The php part works as what I think is needed. But the query does not. I am not very good at MySQL queries so don't judge.
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.