Search Results

Search found 14016 results on 561 pages for 'mysql like'.

Page 179/561 | < Previous Page | 175 176 177 178 179 180 181 182 183 184 185 186  | Next Page >

  • data from few MySQL tables sorted by ASC

    - by Andrew
    In the dbase I 've few tables named as aaa_9xxx, aaa_9yyy, aaa_9zzz. I want to find all data with a specified DATE and show it with the TIME ASC. First, I must find a tables in the dbase: $STH_1a = $DBH->query("SELECT table_name FROM information_schema.tables WHERE table_name LIKE 'aaa\_9%' "); foreach($STH_1a as $row) { $table_name_s1[] = $row['table_name']; } Second, I must find a data wit a concrete date and show it with TIME ASC: foreach($table_name_s1 as $table_name_1) { $STH_1a2 = $DBH->query("SELECT * FROM `$table_name_1` WHERE date = '2011-11-11' ORDER BY time ASC "); while ($row = $STH_1a2->fetch(PDO::FETCH_ASSOC)) { echo " ".$table_name_1."-".$row['time']."-".$row['ei_name']." <br>"; } } .. but it shows the data sorted by tables name, then by TIME ASC. I must to have all this data (from all tables) sorted by TIME ASC. Thank You dev-null-dweller, Andrew Stubbs and Jaison Erick for your help. I test the Erick solution : foreach($STH_1a as $row) { $stmts[] = sprintf('SELECT * FROM %s WHERE date="%s"', $row['table_name'], '2011-11-11'); } $stmt = implode("\nUNION\n", $stmts); $stmt .= "\nORDER BY time ASC"; $STH_1a2 = $DBH->query($stmt); while ($row_1a2 = $STH_1a2->fetch(PDO::FETCH_ASSOC)) { echo " ".$row['table_name']."-".$row_1a2['time']."-".$row_1a2['ei_name']." <br>"; } it's working but I've problem with 'table_name' - it's always the LAST table name. //---------------------------------------------------------------------- end the ending solution with all fixes, thanks all for your help, :)) foreach($STH_1a as $row) { $stmts[] = sprintf("SELECT *, '%s' AS table_name FROM %s WHERE date='%s'", $row['table_name'], $row['table_name'], '2011-11- 11'); } $stmt = implode("\nUNION\n", $stmts); $stmt .= "\nORDER BY time ASC"; $STH_1a2 = $DBH->query($stmt); while ($row_1a2 = $STH_1a2->fetch(PDO::FETCH_ASSOC)) { echo " ".$row_1a2['table_name']."-".$row_1a2['time']."-".$row_1a2['ei_name']." <br>"; }

    Read the article

  • Add results from MySQL?

    - by NardCake
    Not quite sure how descriptive that title was but this is what I want to do. I scripted a URL shortener today and it's working fine, I just want to add some stats on the bottom of it saying how many links there are and how many clicks there are. Now everytime a user clicks one of the links it +1 the column in the database then it redirects the user. I want to query that and add all of those numbers together from each row. I attempted a while loop which im not surprised didn't work: while($rows = mysql_fetch_assoc($check_count)){ $clicks = $rows['clicks']; $clicks = $clicks+$clicks; } If you don't understand please ask and if you do understand it means alot for any response!

    Read the article

  • codeigniter active record and mysql

    - by sea_1987
    I am running a query with Active Record in a modal of my codeigniter application, the query looks like this, public function selectAllJobs() { $this->db->select('*') ->from('job_listing') ->join('job_listing_has_employer_details', 'job_listing_has_employer_details.employer_details_id = job_listing.id', 'left'); //->join('employer_details', 'employer_details.users_id = job_listing_has_employer_details.employer_details_id'); $query = $this->db->get(); return $query->result_array(); } This returns an array that looks like this, [0]=> array(13) { ["id"]=> string(1) "1" ["job_titles_id"]=> string(1) "1" ["location"]=> string(12) "Huddersfield" ["location_postcode"]=> string(7) "HD3 4AG" ["basic_salary"]=> string(19) "£20,000 - £25,000" ["bonus"]=> string(12) "php, html, j" ["benefits"]=> string(11) "Compnay Car" ["key_skills"]=> string(1) "1" ["retrain_position"]=> string(3) "YES" ["summary"]=> string(73) "Lorem Ipsum is simply dummy text of the printing and typesetting industry" ["description"]=> string(73) "Lorem Ipsum is simply dummy text of the printing and typesetting industry" ["job_listing_id"]=> NULL ["employer_details_id"]=> NULL } } The job_listing_id and employer_details_id return as NULL however if I run the SQL in phpmyadmin I get full set of results, the query i running in phpmyadmin is, SELECT * FROM ( `job_listing` ) LEFT JOIN `job_listing_has_employer_details` ON `job_listing_has_employer_details`.`employer_details_id` LIMIT 0 , 30 Is there a reason why I am getting differing results?

    Read the article

  • Need help optimizing MYSQL query with join

    - by makeee
    I'm doing a join between the "favorites" table (3 million rows) the "items" table (600k rows). The query is taking anywhere from .3 seconds to 2 seconds, and I'm hoping I can optimize it some. Favorites.faver_profile_id and Items.id are indexed. Instead of using the faver_profile_id index I created a new index on (faver_profile_id,id), which eliminated the filesort needed when sorting by id. Unfortunately this index doesn't help at all and I'll probably remove it (yay, 3 more hours of downtime to drop the index..) Any ideas on how I can optimize this query? In case it helps: Favorite.removed and Item.removed are "0" 98% of the time. Favorite.collection_id is NULL about 80% of the time. SELECT `Item`.`id`, `Item`.`source_image`, `Item`.`cached_image`, `Item`.`source_title`, `Item`.`source_url`, `Item`.`width`, `Item`.`height`, `Item`.`fave_count`, `Item`.`created` FROM `favorites` AS `Favorite` LEFT JOIN `items` AS `Item` ON (`Item`.`removed` = 0 AND `Favorite`.`notice_id` = `Item`.`id`) WHERE ((`faver_profile_id` = 1) AND (`collection_id` IS NULL) AND (`Favorite`.`removed` = 0) AND (`Item`.`removed` = '0')) ORDER BY `Favorite`.`id` desc LIMIT 50;

    Read the article

  • MySQL: count enumerated values?

    - by John Isaacks
    If my table looks like this: daily_individual_tracking', 'CREATE TABLE `daily_individual_tracking` ( `daily_individual_tracking_id` int(10) unsigned NOT NULL auto_increment, `daily_individual_tracking_date` date NOT NULL default ''0000-00-00'', `sales` enum(''no'',''yes'') NOT NULL COMMENT ''no'', `repairs` enum(''no'',''yes'') NOT NULL COMMENT ''no'', `shipping` enum(''no'',''yes'') NOT NULL COMMENT ''no'', PRIMARY KEY (`daily_individual_tracking_id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 basically the fields can be either yes or no. How can I count how many yes's their are for each column over a date range? Thanks!!

    Read the article

  • How to query MYSQL when clicked?

    - by Sam
    Hello, I have a while statement, echoing my whole database that match a WHERE parameter. How can I make it so when I click on something (anything for the moment), it updates that specific row. Here's my code. while($request = mysql_fetch_array( $request_db )) { echo "<tr><td style=\"width:33%;padding:1px;\">"; echo $request['SongName']; echo "</td><td style=\"width:33%;\">"; echo $request['Artist']; echo "</td><td style=\"width:33%;\">"; echo $request['DedicatedTo']; echo "</td><td style=\"width:33%;\">"; echo "UPDATE A ROW's 'Hasplayed' value to '1'."; echo "</td></tr>"; } echo "</table>"; Thanks!

    Read the article

  • please check my MYSQL query & give me advice?

    - by Suba
    select s.s_nric as NRIC,s.s_name as NAME,s.s_psle_eng as PSLE_ENG,s.s_psle_math as PSLE_MATHS,s.s_psle_aggr as PSLE_AGGR, (SELECT re.re_mark FROM si_results re WHERE re.re_code like 'FEEN%' AND re.re_year='2008' AND re.re_semester='2' AND re.re_nric=s.s_nric ) as English_2008, (SELECT re.re_mark FROM si_results re WHERE re.re_code like 'FEMA%' AND re.re_year='2008' AND re.re_semester='2' AND re.re_nric=s.s_nric ) Maths_2008, (SELECT re.re_mark FROM si_results re WHERE re.re_code like 'FEEN%' AND re.re_year='2009' AND re.re_semester='2' AND re.re_nric=s.s_nric ) as English_2009, (SELECT re.re_mark FROM si_results re WHERE re.re_code like 'FEMA%' AND re.re_year='2009' AND re.re_semester='2' AND re.re_nric=s.s_nric ) Maths_2009 ,isc.isc_g_gpa as ISC_GPA from si_student_data as s LEFT JOIN si_isc_gpa as isc ON isc.isc_g_nric=s.s_nric where 1=1 AND s.s_admission_year='2008' GROUP BY s.s_nric ORDER BY s.s_gender,s.s_name asc This is my query. please check my sub query this is my sub query (SELECT re.re_mark FROM si_results re WHERE re.re_code like 'FEEN%' AND re.re_year='2008' AND re.re_semester='2' AND re.re_nric=s.s_nric ) as English_2008, (SELECT re.re_mark FROM si_results re WHERE re.re_code like 'FEMA%' AND re.re_year='2008' AND re.re_semester='2' AND re.re_nric=s.s_nric ) Maths_2008, (SELECT re.re_mark FROM si_results re WHERE re.re_code like 'FEEN%' AND re.re_year='2009' AND re.re_semester='2' AND re.re_nric=s.s_nric ) as English_2009, (SELECT re.re_mark FROM si_results re WHERE re.re_code like 'FEMA%' AND re.re_year='2009' AND re.re_semester='2' AND re.re_nric=s.s_nric ) Maths_2009 When I execute my query, server take long time to execute. So how to make simple? please advice me. Thanks.

    Read the article

  • Validate a date range within MySQL query

    - by fishcracker
    (This question may seem easy or kind of noobish, by that I pardon my ignorance.) I used PDO query to use SELECT then fetch some values, it comes to a point that I need to fetch only some entries that within its start date and end date. My database +----------+-----------------+----------------------+--------------------+ | id (INT) | title (VARCHAR) | start_date (VARCHAR) | end_date (VARCHAR) | +----------+-----------------+----------------------+--------------------+ | 1 | buddy | 2012-11-26 | 2012-11-30 | | 2 | metro | 2012-12-05 | 2012-12-20 | | 3 | justin | 2012-11-28 | 2012-12-01 | +----------+-----------------+----------------------+--------------------+ My query is as follows: $query = "SELECT title, start_date, end_date FROM debts WHERE start_date >= CURDATE() AND end_date >= CURDATE()"; What I want to achieve is whenever the start_date is today or greater but not exceeding the end_date it will be valid. This will return the row for id 1, however if I change the start_date to 2012-11-25, it will fail due to the first condition on AND. I'm really confuse on this since I am new to this, is there any built-in function to handle this kind of situation?

    Read the article

  • MYSQL, Subquery Reference in Union

    - by christian
    Is there any way to reference a subquery in a union? I am trying to do something like the following, and would like to avoid a temporary table, but the subquery will be drawn from a much larger dataset so it makes sense to only do it once.. SELECT * FROM (SELECT * FROM ads WHERE state='FL' AND city='Maitland' AND page='home' ORDER BY RAND()) AS sq WHERE spot = 'full-banner' LIMIT 1 UNION SELECT * FROM sq WHERE spot = 'leaderboard' LIMIT 1 UNION SELECT * FROM sq WHERE spot = 'rectangle1' LIMIT 1 UNION SELECT * FROM sq WHERE spot = 'rectangle2' LIMIT 1 .... etc,, It's a shame that DISTINCT can't be specified for a single column of a result set.

    Read the article

  • Problem in Union Join For MySQL Query

    - by Mac Taylor
    hey guys i managed to select from a table that saves my latest posts but i need to have double condition in selection here is my code : $sql_query = "SELECT b.*,u.username AS MY_Sender FROM TABLE_users u,TABLE_blogs b Where b.reciever = '0' AND u.user_id = b.sender UNION SELECT b.*,u.username AS MY_reciever FROM TABLE_users u,TABLE_blogs b Where b.reciever != '0' AND u.user_id = b.reciever ORDER BY bid DESC LIMIT 0,7 "; but MY_reciever is Null and empty Am i wrong in using UNION for this need ?!

    Read the article

  • mysql left join

    - by user1019538
    I have two table one is index and another is the price structure as under table : index : column : trandate ,indexcode Table : price : Column: trandate,symbol,price i want to know the missing price. I issue the query select i.trandate,i.indexcode,p.trandate,p.price from index i left join price p on i.trandate = p.trandate where p.symbol='ABC' and indexcode="New" the above query does not show the null date even though various price in missing in price table. Only reason i understand is that the index table does not have the symbol field that's why...but as per theory if you want to show all the rows of one table and only the match value of another table then use the left or right join query...please anybody can help

    Read the article

  • In PHP + MySQL, How do I join many tables with conditions

    - by Moe
    Hi, I'm trying to get the users full activity throughout the website. I need to Join many tables throughout the database, with that condition that it is one user. What I currently have written is: SELECT * FROM comments AS c JOIN rphotos AS r ON c.userID = r.userID AND c.userID = '$defineUserID'; But What it is returning is everything about the user, but it repeats rows. For instance, for one user he has 6 photos and 5 comments So I expect the join to return 11 rows. Instead it returns 30 results like so: PhotoID = 1; CommentID = 1; PhotoID = 1; CommentID = 2; PhotoID = 1; CommentID = 3; and so on... What am i doing wrong?

    Read the article

  • MySQL count problem

    - by Skuja
    I have 3 tables users(id,name),groups(id,name) and users_groups(user_id,group_id). users and groups have many to many relationship, so the third one is for storing users and groups relations. I would like to select all the data from groups with user count in each group. So far I came up with this: SELECT groups.*, COUNT(users_groups.user_id) AS user_count FROM groups LEFT JOIN users_groups ON users_groups.group_id = groups.id The problem is that query result is not returning any of groups which has no users (users_groups doesnt have any records with group_id of those groups). How should I create my query to select all the groups and they user count, or user count as 0 if there are no users for that group?

    Read the article

  • Help needed with simple mysql group by query

    - by Jack
    This query fails when I add the line shown... :BEGIN FAIL: I have this so far Select Companyid, count(*) as cnt from mytable where State is not null and cnt = 1 <------------------------- FAIL group by CompanyID :END FAIL: Any way to do this? Here's a long winded background if it'll help.... I have a single table query. here's a sample of the table: CompanyID, State 1,OH 1,IL 1,NY 2,IL 3,NY 3,OH 4,NY 5,CA 5,WA I want a query that'll return something like this: 2,IL 4,NY I have this so far Select Companyid, count(*) as cnt from mytable where State is not null group by CompanyID This gives me a count of the number of records for each company. IE: 1,3 2,1 3,2 4,1 5,2 Now I want to filter the above list to just the two records with one result. I tried adding another where clause, but it failed: BEGIN FAIL: I have this so far Select Companyid, count(*) as cnt from mytable where State is not null and cnt = 1 <-------------------- FAIL group by CompanyID END FAIL:

    Read the article

  • MySQL: Limit output according to associated ID

    - by Jess
    So here's my situation. I have a books table and authors table. An author can have many books... In my authors page view, the user (logged in) can click an author in a tabled row and be directed to a page displaying the author's books (collected like this URI format: viewauthorbooks.php?author_id=23), very straight forward... However, in my query, I need to display the books for the author only, and not all books stored in the books table (as i currently have!) As I am a complete novice, I used the most simple query of: SELECT * FROM tasks_tb This returns the books for me, but returns every single value (book) in the database, and not ones associated with the selected author. And when I click a different author the same books are displayed for them...I think everyone gets what I'm trying to achieve, I just don't know how to perform the query. I'm guessing that I need to start using more advanced query clauses like INNER JOIN etc. Anyone care to help me out :)

    Read the article

  • Adding with PHP to a MySQL database

    - by shinjuo
    I am pretty new to PHP and I am trying to make an inventory database. I have been trying to make it so that a user can enter a card ID and then amount the want to add to the inventory and have it update the inventory. For example someone could type in test and 2342 and it would update test. Here is what I have been trying with no success: add.html <body> <form action="add.php" method="post"> Card ID: <input type="text" name="CardID" /> Amount to Add: <input type="text" name="Add" /> <input type="submit" /> </form> </body> </html> add.php <?php $link = mysql_connect('tulsadir.ipowermysql.com', 'cbouwkamp', '!starman1'); if (!$link){ die('Could not connect: ' . mysql_error()); } mysql_select_db("tdm_inventory", $link); $add = $_POST[Add] mysql_query("UPDATE cardLists SET AmountLeft = '$add' WHERE cardID = 'Test'"); echo "test successful"; mysql_close($link); ?>

    Read the article

  • Strange use of the index in Mysql

    - by user309067
    explain SELECT feed_objects.* FROM feed_objects WHERE (feed_objects.feed_id IN (165,160,159,158,157,153,152,151,150,149,148,147,129,128,127,126,125,124,122,121,120,119,118,117,116,115,114,113,111,110)) ; +----+-------------+--------------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | feed_objects | ALL | by_feed_id | NULL | NULL | NULL | 188 | Using where | +----+-------------+--------------+------+---------------+------+---------+------+------+-------------+ Not used index 'by_feed_id' But when I point less than the values in the "WHERE" - everything is working right explain SELECT feed_objects.* FROM feed_objects WHERE (feed_objects.feed_id IN (165,160,159,158,157,153,152,151,150,149,148,147,129,128,127,125,124)) ; +----+-------------+--------------+-------+---------------+------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+-------+---------------+------------+---------+------+------+-------------+ | 1 | SIMPLE | feed_objects | range | by_feed_id | by_feed_id | 9 | NULL | 18 | Using where | +----+-------------+--------------+-------+---------------+------------+---------+------+------+-------------+ Used index 'by_feed_id' What is the problem?

    Read the article

  • Best database (mysql) structure for this case:

    - by robert
    we have three types of data (tables): Book (id,name,author...) ( about 3 million of rows) Category (id,name) ( about 2000 rows) Location (id,name) ( about 10000 rows) A Book must have at least 1 type of Category (up to 3) AND a Book must have only one Location. I need to correlate this data to get this query faster: Select Books where Category = 'cat_id' AND Location = 'loc_id' Select Books where match(name) against ('name of book') AND Location = 'loc_id' Please I need some help. Thanks

    Read the article

< Previous Page | 175 176 177 178 179 180 181 182 183 184 185 186  | Next Page >