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


Forums

  • 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

    End


Last Updated

  • Start

    End


Filter by number of...

Joined

  • Start

    End


Group


AIM


MSN


Website URL


ICQ


Yahoo


Jabber


Skype


Location


Interests


Age


Donation Link

Found 17 results

  1. 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 !
  2. 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(); } ?>
  3. 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.'; } }
  4. 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
  5. 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...?
  6. 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?
  7. 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?
  8. 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 } } }
  9. 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
  10. 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.
  11. 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; } } }
  12. I have a query below where I want to search a table to find the top row with 1 or 2 empty positions. But it never returns the correct "referral_id". If I remove the "ref_user_1" and "ref_user_2" conditions from the query, then of course it will return the correct referral id. But those conditions are important for me to have. Is having "AND" and "OR" conditions in the same query against the rules? If so what's the solution for this problem? $find_sponsor = $db->prepare("SELECT * FROM referrals WHERE referred_by = :referred_by AND ref_user_1 = :ref_user_1 OR ref_user_2 = :ref_user_2 ORDER BY referral_id ASC LIMIT 1"); $find_sponsor->bindValue(':referred_by', 2); $find_sponsor->bindValue(':ref_user_1', 0); $find_sponsor->bindValue(':ref_user_2', 0); $find_sponsor->execute(); $result_sponsor = $find_sponsor->fetchAll(PDO::FETCH_ASSOC); if(count($result_sponsor) > 0) { foreach($result_sponsor as $row) { $get_referral_id = $row['referral_id']; $get_ref_1 = $row['ref_user_1']; $get_ref_2 = $row['ref_user_2']; } echo $get_referral_id; // this always returns the wrong referral id } // Table referral_id referred_by ref_user_1 ref_user_2 1 2 20 21 2 2 23 24 3 2 25 0 4 2 0 0 As per my table above, what would my new query look like if I want to retrieve #3 as my referral id?
  13. Hi Guys, I have a JET SQL query that i need to convert to MYSQL (Appologies too if this is posted in the wrong section.. i never know whether to go PHP or MYSQL!) Im not being lazy.. ive tried for hours but cannot get it to work, it has two depth inner join and a group by with a where (with 1 criteria)... If anyone can help id massively appreciate it and also explain how you got there... SELECT Count(tbl_Items.ItemID) AS CountOfItemID, tbl_LU_Collections.CollectionDesc FROM (tbl_Items LEFT JOIN tbl_LU_Categories ON tbl_Items.ItemCategory = tbl_LU_Categories.ItemCatID) LEFT JOIN tbl_LU_Collections ON tbl_LU_Categories.CollectionID = tbl_LU_Collections.CollectionID WHERE (((tbl_Items.RetailProduct)=-1)) GROUP BY tbl_LU_Collections.CollectionDesc;
  14. I would like to simplify my request... here is my query SELECT emp_id, ROUND(SUM((Invoice.invoiceTotal-NewQuote.total)*.30),2) AS thirtypercent, ROUND(SUM(NewQuote.total*.20),2) AS twentypercent FROM `NewQuote` INNER JOIN Invoice ON Invoice.quoteID=NewQuote.quoteID INNER JOIN schedules ON schedules.quoteId=NewQuote.quoteID WHERE (rem=1 OR rem=2) AND (schedules.redo IS NULL) AND invoiceTotal!=0 AND (DATE_FORMAT(date_start, '%m/%d/%Y') >= '07/01/2016') AND ((DATE_FORMAT(date_start, '%m/%d/%Y')<='07/31/2016')) GROUP BY emp_id What im aiming here is this line "AND invoiceTotal!=0" should only be used in getting the thirtypercent and should not be applied in getting the twentypercent. Can you help me on how i can do this? Thank you
  15. I have two tables. Table-1 is Campaigns and Table-2 is Earnings. I simply want to list the Campaigns from high to low earnings or vice versa. But I don't know how to do that since it's two separate tables. Here are the two queries I have. The query shows that the Campaigns will be listed by their campaign_id. I want to be able to order them by their earnings. How do I do that with these two queries? $find_campaign = $db->prepare("SELECT * FROM campaigns WHERE status = :status ORDER BY campaign_id DESC LIMIT 10"); $find_campaign->bindValue(':status', 1); $find_campaign->execute(); $result_find_campaign = $find_campaign->fetchAll(PDO::FETCH_ASSOC); if(count($result_find_campaign) > 0) { foreach($result_find_campaign as $row) { $campaign_id = trim($row['campaign_id']); $campaign_goal = trim($row['campaign_goal']); $get_raised = $db->prepare("SELECT deposit FROM earnings WHERE campaign_id = :campaign_id"); $get_raised->bindParam(':campaign_id', $campaign_id); $get_raised->execute(); $result_raised = $get_raised->fetchAll(PDO::FETCH_ASSOC); if(count($result_raised) > 0) { $ub = 0; foreach($result_raised as $key=>$row) { $campaign_raised = trim($row['deposit']); } } else { $campaign_raised = 0; } //show campaign html here } }
  16. Hello, I am doing my best to learn mysql but I still have a lot to learn when queries involve loops or complex syntax. Here's what I would like to achieve: Table 1 includes games played since the beginning of the season (where homeTeamScore or awayTeamScore > 0): Table 2 includes predictions made by users as to which team will win each game (gameId in T2 = id in T1): The following query lets me know how many games have been played since the beginning of the season: $query = "SELECT COUNT(`id`) FROM " . SPORTS_BOL_GameDao::getInstance()->getTableName() . " WHERE SeasonID = " . $seasonId . " AND (homeTeamScore > 0 OR awayTeamScore > 0) ORDER BY gametime DESC"; $totalGames = $this->dbo->queryForColumn($query); And this one how many predictions each user has registered: $query = "SELECT COUNT(`g`.`id`) FROM " . SPORTS_BOL_GameDao::getInstance()->getTableName() . " g INNER JOIN " . SPORTS_BOL_PredictionDao::getInstance()->getTableName() . " p ON g.id = p.gameId WHERE g.SeasonID = " . $seasonId . " AND (g.homeTeamScore > 0 OR g.awayTeamScore > 0) AND p.userId = " . $point['userId'] . " ORDER BY g.gametime DESC"; $totalPreds = $this->dbo->queryForColumn($query); First question: how could I combine these two queries into one and get the total number of missing predictions for each user? As an example, if 89 games have been played and user has registered 70 predictions, I would like to get "19" out of the query. Second question, more complex (at least to me!): what would the query look like if I wanted to know how many consecutive games has each user missed since their last prediction? As an example, if my last prediction was registered on gameId "460" and 4 more games have been played since then (id 468, 469, 470 and 471), I would like to get "4" out of the query. Hope this makes sense and is easy to understand! Thank you very much for your support!
  17. Say I have this records table. RECORDS TABLE record_id | sponsor_id | user_id | plan_id ------------------------------------------------------------------------------ 1 user5 user6 5 // I am this user. 2 user3 user5 3 3 user3 user4 4 4 user2 user3 4 5 user2 user2 2 6 user0 user1 5 I am "user6" and my sponsor is "user5". What I want to do is find the same "plan_id" from my sponsors, no matter how far up I have to do. For eg. My current plan id is "5". My sponsor is "user5". If I look for "user5" in the user_id column, I would find that he only has plan "3" id. So I go to his sponsor, which is "user3" and find him in the user_id column. That user's plan id is "4" so it does not match my plan id either. I repeat the same process by going to his sponsor and his sponsor and so on until I find the plan id that matches me. So for this table example, that would be "user1". I only want to retrieve the first result that matches my plan id. How do I go on about coding this function? Normally I can do these queries to go up limited amount. But I am looking for a more proper function that lets me search my sponsors unlimited times. $find_plan_id = $db->prepare("SELECT sponsor_id, plan_id FROM records WHERE user_id = :user_id"); $find_plan_id->bindParam(':user_id', $user_id); $find_plan_id->execute(); $result_find_plan_id = $find_plan_id->fetchAll(PDO::FETCH_ASSOC); if(count($result_plan_id) > 0) { foreach($result_plan_id as $row) { $get_sponsor_id_1 = $row['sponsor_id']; $get_plan_id_1 = $row['plan_id']; } if($get_plan_id_1 == $my_plan_id) { echo 'Plan id matches.'; } else { $find_plan_id_2 = $db->prepare("SELECT sponsor_id, plan_id FROM records WHERE user_id = :user_id"); $find_plan_id_2->bindParam(':user_id', $get_sponsor_id_1); $find_plan_id_2->execute(); $result_plan_id_2 = $find_plan_id_2->fetchAll(PDO::FETCH_ASSOC); if(count($result_plan_id_2) > 0) { foreach($result_plan_id_2 as $row) { $get_sponsor_id_2 = $row['sponsor_id']; $get_plan_id_2 = $row['plan_id']; } if($get_plan_id_2 == $my_plan_id) { echo 'Plan id matches.'; } else { // repeat the process } } } }
×
×
  • 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.