Jump to content

Search the Community

Showing results for tags 'query'.

  • 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 10 results

  1. 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?
  2. 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 !
  3. 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 } } }
  4. 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?
  5. 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; } } }
  6. 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.
  7. 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 } }
  8. 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;
  9. 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 } } } }
  10. 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?
×
×
  • 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.