Search Results

Search found 23901 results on 957 pages for 'mysql stored procedure'.

Page 388/957 | < Previous Page | 384 385 386 387 388 389 390 391 392 393 394 395  | Next Page >

  • Basic join query understanding

    - by OM The Eternity
    I know this very silly, but can anybody help me in understanding what does this join query is doing in elabortive description? 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 j1.trackid=$t_ids[$n] AND j2.id IS NULL I know its very silly, but i need to go ahead with my further need... Pls do help me...

    Read the article

  • SQL Query Returning Duplicate Results

    - by Jesse Bunch
    Hi, I've been working out this query now for a while and I thought I had it where I wanted it, but apparently not. There are two records in the database (orders). The query should return two different rows, but instead returns two rows that have exactly the same values. I think it may be something to do with the GROUP BY or derived tables I'm using but my eyes are tired and not seeing the problem. Can any of you help? Thanks in advance. SELECT orders.billerID, orders.invoiceDate, orders.txnID, orders.bName, orders.bStreet1, orders.bStreet2, orders.bCity, orders.bState, orders.bZip, orders.bCountry, orders.sName, orders.sStreet1, orders.sStreet2, orders.sCity, orders.sState, orders.sZip, orders.sCountry, orders.paymentType, orders.invoiceNotes, orders.pFee, orders.shipping, orders.tax, orders.reasonCode, orders.txnType, orders.customerID, customers.firstName AS firstName, customers.lastName AS lastName, customers.businessName AS businessName, orderStatus.statusName AS orderStatus, IFNULL(orderItems.itemTotal, 0.00) + orders.shipping + orders.tax AS orderTotal, IFNULL(orderItems.itemTotal, 0.00) + orders.shipping + orders.tax - IFNULL(payments.totalPayments, 0.00) AS orderBalance FROM orders LEFT JOIN customers ON orders.customerID = customers.id LEFT JOIN orderStatus ON orders.orderStatus = orderStatus.id LEFT JOIN ( SELECT orderItems.orderID, SUM(orderItems.itemPrice * orderItems.itemQuantity) as itemTotal FROM orderItems GROUP BY orderItems.orderID ) orderItems ON orderItems.orderID = orders.id LEFT JOIN ( SELECT payments.orderID, SUM(payments.amount) as totalPayments FROM payments GROUP BY payments.orderID ) payments ON payments.orderID = orders.id

    Read the article

  • SQL join produces one result only

    - by Rami
    Can anyone please tell me why this result is generation only one results? taking in mind that everything is set right and the three tables are populated correctly, i took out the group_concat and it worked but of course with a php undefined index error! SELECT `songs`.`song_name`, `songs`.`add_date`, `songs`.`song_id`, `songs`.`song_picture`, group_concat(DISTINCT artists.artist_name) as artist_name FROM (`songs`) JOIN `mtm_songs_artists` ON `songs`.`song_id` = `mtm_songs_artists`.`song_id` JOIN `artists` ON `artists`.`artist_id` = `mtm_songs_artists`.`artist_id` ORDER BY `songs`.`song_id` DESC LIMIT 10 so i'm guessing it's something related to group_concat. best regards, Rami

    Read the article

  • Using memtables in sql. When is it reasonable and is it safe?

    - by Spiros
    I was just reading an update from a friend's project, mentioning the use of memtables to store data temporatily and then flush to a table on disk. Up to now, I have never faced a situation where I would use a memtable, or a situation where I would think the use of a mem table would be beneficial; so I wonder, when would someone use mem tables? what makes a memtable (appart from access speed) a reasonable choice? and how safe is it, even for temp data? there is always the limitation of available physical memory.

    Read the article

  • How would I UPDATE these table entries with SQL?

    - by CT
    I am working on an Asset Database problem. I enter assets into a database. Every object is an asset and has variables within the asset table. An object is also a type of asset. In this example the type is server. Here is the Query to retrieve all necessary data: SELECT asset.id ,asset.company ,asset.location ,asset.purchaseDate ,asset.purchaseOrder ,asset.value ,asset.type ,asset.notes ,server.manufacturer ,server.model ,server.serialNumber ,server.esc ,server.warranty ,server.user ,server.prevUser ,server.cpu ,server.memory ,server.hardDrive FROM asset LEFT JOIN server ON server.id = asset.id WHERE asset.id = '$id' How would I write a query to update an asset?

    Read the article

  • Select * from 'many to many' SQL relationship

    - by Rampant Creative Group
    I'm still learning SQL and my brain is having a hard time with this one. Say I have 3 tables: teams players and teams_players as my link table All I want to do is run a query to get each team and the players on them. I tried this: SELECT * FROM teams INNER JOIN teams_players ON teams.id = teams_players.team_id INNER JOIN players ON teams_players.player_id = players.id But it returned a separate row for each player on each team. Is JOIN the right way to do it or should I be doing something else? ----------------------------------------- Edit Ok, so from what I'm hearing, this isn't necessarily a bad way to do it. I'll just have to group the data by team while I'm doing my loop. I have not yet tried the modified SQL statements provided, but I will today and get back to you. To answer the question about structure - I guess I wasn't thinking about the returned row structure which is part of what lead to my confusion. In this particular case, each team is limited to 4 players (or less) so I guess the structure that would be helpful to me is something like the following: teams.id, teams.name, players.id, players.name, players.id, players.name, players.id, players.name, players.id, players.name, 1 Team ABC 1 Jim 2 Bob 3 Ned 4 Roy 2 Team XYZ 2 Bob 3 Ned 5 Ralph 6 Tom

    Read the article

  • Webmail system script

    - by panidarapu
    Hello, I am planning to launch a free webmail service at mail(dot)com(dot)es where users can signup and have their free webmail account (for example like www.email.gr). I am looking for a free or low cost webmail system script to install on my website. Do you know any free or low cost scripts which would be suitable for this? Thanks

    Read the article

  • Table with a lot of attributes

    - by Robert
    Hi, I'm planing to build some database project. One of the tables have a lot of attributes. My question is: What is better, to divide the the class into 2 separate tables or put all of them into one table. below is an example create table User { id, name, surname,... show_name, show_photos, ...) or create table User { id, name, surname,... ) create table UserPrivacy {usr_id, show_name, show_photos, ...) The performance i suppose is similar due to i can use index.

    Read the article

  • Using * in SELECT Query

    - by libregeek
    I am currently porting an application written in MySQL3 and PHP4 to MySQL5 and PHP5. On analysis I found several SQL queries which uses "select * from tablename" even if only one column(field) is processed in PHP. The table has almost 60 columns and it has a primary key. In most cases, the only column used is id which is the primary key. Will there be any performance boost if I use queries in which the column names are explicitly mentioned instead of * ? (In this application there is only one method which we need all the columns and all other methods return only a subset of the columns)

    Read the article

  • PHP replace string help...

    - by MILESMIBALERR
    i am designing a site with a comment system and i would like a twitter like reply system. The if the user puts @a_registered_username i would like it to become a link to the user's profile. i think preg_replace is the function needed for this. $ALL_USERS_ROW *['USERNAME'] is the database query array for all the users and ['USERNAME'] is the username row. $content is the comment containing the @username i think this should not be very hard to solve for someone who is good at php. Does anybody have any idea how to do it?

    Read the article

  • PHP function returns ZERO.

    - by hypnocode
    Hi guys, I'm having trouble with this PHP function. It keeps returning zero, but I know the SQL statement works because I've queried it myself. Any ideas what I'm doing wrong? The last line makes no sense to me...I'm editing this code that someone else wrote. This function should return a number in the hundreds, assuming the date is in March. Thanks! function getCountBetweenDays($day1,$day2,$service) { global $conn; if ($service==1){ $query = "SELECT COUNT(*) as NUM FROM `items` WHERE `modified` BETWEEN '$day1 00:00:00' AND '$day2 23:59:59';";} elseif($service==2){ $query = "SELECT COUNT(*) as NUM FROM `items` WHERE `modified` BETWEEN '$day1 00:00:00' AND '$day2 23:59:59';";} elseif($service==3){ $query = "SELECT COUNT(*) as NUM FROM `items` WHERE `modified` BETWEEN '$day1 00:00:00' AND '$day2 23:59:59';";} $result = mysql_query($query,$conn); $num = mysql_fetch_array ($result); return $num['NUM']; }

    Read the article

  • Sql query - selecting top 5 rows and further selecting rows only if User is present

    - by Gublooo
    Hello, I kind of stuck on how to implement this query - this is pretty similar to the query I posted earlier but I'm not able to crack it. I have a shopping table where everytime a user buys anything, a record is inserted. Some of the fields are * shopping_id (primary key) * store_id * user_id Now what I need is to pull only the list of those stores where he's among the top 5 visitors: When I break it down - this is what I want to accomplish: * Find all stores where this UserA has visited * For each of these stores - see who the top 5 visitors are. * Select the store only if UserA is among the top 5 visitors. The corresponding queries would be: select store_id from shopping where user_id = xxx select user_id,count(*) as 'visits' from shopping where store_id in (select store_id from shopping where user_id = xxx) group by user_id order by visits desc limit 5 Now I need to check in this resultset if UserA is present and select that store only if he's present. For example if he has visited a store 5 times - but if there are 5 or more people who have visited that store more than 5 times - then that store should not be selected. So I'm kind of lost here. Thanks for your help

    Read the article

  • Best way to implement keywords for image upload gallery

    - by Dan Berlyoung
    I'm starting to spec out an image gallery type system similar to Facebook's. Members of the site will be able to create image galleries and upload images for others to view. Images will have keywords the the uploader can specify. Here's the question, what's the best way to model this? With image and keyword tables linked vi a HABTM relation? Or a single image table with the keywords saved as comma delimited values in a text field in the image record? Then search them using a LIKE or FULL TEXT index function? I want to be able to pull up all images containing a given keyword as well as generate a keyword cloud. I'm leaning toward the HABTM setup but I wanted to see what everyone else though. Thanks!!

    Read the article

  • Is there a way to optimize this update query?

    - by SchlaWiener
    I have a master table called "parent" and a related table called "childs" Now I run a query against the master table to update some values with the sum from the child table like this. UPDATE master m SET quantity1 = (SELECT SUM(quantity1) FROM childs c WHERE c.master_id = m.id), quantity2 = (SELECT SUM(quantity2) FROM childs c WHERE c.master_id = m.id), count = (SELECT COUNT(*) FROM childs c WHERE c.master_id = m.id) WHERE master_id = 666; Which works as expected but is not a good style because I basically make multiple SELECT querys on the same result. Is there a way to optimize that? (Making a query first and storing the values is not an option. I tried this: UPDATE master m SET (quantity1, quantity2, count) = ( SELECT SUM(quantity1), SUM(quantity2), COUNT(*) FROM childs c WHERE c.master_id = m.id ) WHERE master_id = 666; but that doesn't work.

    Read the article

  • Creating search functionality with Laravel 4

    - by Mitch Glenn
    I am trying to create a way for users to search through all the products on a website. When they search for "burton snowboards", I only want the snowboards with the brand burton to appear in the results. But if they searched only "burton", then all products with the brand burton should appear. This is what I have attempted to write but isn't working for multiple reasons. Controller: public function search(){ $input = Input::all(); $v= Validator::make($input, Product::$rules); if($v->passes()) { $searchTerms = explode(' ', $input); $searchTermBits = array(); foreach ($searchTerms as $term) { $term = trim($term); if (!empty($term)){ $searchTermBits[] = "search LIKE '%$term%'"; } } $result = DB::table('products') ->select('*') ->whereRaw(". implode(' AND ', $searchTermBits) . ") ->get(); return View::make('layouts/search', compact('result')); } return Redirect::route('/'); } I am trying to recreate the first solution given for this stackoverflow.com problem The first problem I have identified is that i'm trying to explode the $input, but it's already an array. So i'm not sure how to go about fixing that. And the way I have written the ->whereRaw(". implode(' AND ', $searchTermBits) . "), i'm sure isn't correct. I'm not sure how to fix these problems though, any insights or solutions will be greatly appreciated.

    Read the article

  • SQLAlchemy: select over multiple tables

    - by ahojnnes
    Hi, I wanted to optimize my database query: link_list = select( columns=[link_table.c.rating, link_table.c.url, link_table.c.donations_in], whereclause=and_( not_(link_table.c.id.in_( select( columns=[request_table.c.recipient], whereclause=request_table.c.donator==donator.id ).as_scalar() )), link_table.c.id!=donator.id, ), limit=20, ).execute().fetchall() and tried to merge those two selects in one query: link_list = select( columns=[link_table.c.rating, link_table.c.url, link_table.c.donations_in], whereclause=and_( link_table.c.active==True, link_table.c.id!=donator.id, request_table.c.donator==donator.id, link_table.c.id!=request_table.c.recipient, ), limit=20, order_by=[link_table.c.rating.desc()] ).execute().fetchall() the database-schema looks like: link_table = Table('links', metadata, Column('id', Integer, primary_key=True, autoincrement=True), Column('url', Unicode(250), index=True, unique=True), Column('registration_date', DateTime), Column('donations_in', Integer), Column('active', Boolean), ) request_table = Table('requests', metadata, Column('id', Integer, primary_key=True, autoincrement=True), Column('recipient', Integer, ForeignKey('links.id')), Column('donator', Integer, ForeignKey('links.id')), Column('date', DateTime), ) There are several links (donator) in request_table pointing to one link in the link_table. I want to have links from link_table, which are not yet "requested". But this does not work. Is it actually possible, what I'm trying to do? If so, how would you do that? Thank you very much in advance!

    Read the article

  • social networking website database management

    - by Anup Prakash
    This could be very basic type of question for you! But for me it is very important. 1) How these(orkut, facebook or other) website store the images in server? Options: a) Keeping all the images in database by converting into bytecode/binary. b) Making a new folder for each user and saving photographs according to their library name. c) Or something else which i(Anup) didn't guess yet. Please reply me. Sayiing thanx to see my question and any many many thanx for answering my question.

    Read the article

  • Database Error Handling: What if You have to Call Outside service and the Transaction Fails?

    - by Ngu Soon Hui
    We all know that we can always wrap our database call in transaction ( with or without a proper ORM), in a form like this: $con = Propel::getConnection(EventPeer::DATABASE_NAME); try { $con->begin(); // do your update, save, delete or whatever here. $con->commit(); } catch (PropelException $e) { $con->rollback(); throw $e; } This way would guarantee that if the transaction fails, the database is restored to the correct status. But the problem is that let's say when I do a transaction, in addition to that transaction, I need to update another database ( an example would be when I update an entry in a column in databaseA, another entry in a column in databaseB must be updated). How to handle this case? Let's say, this is my code, I have three databases that need to be updated ( dbA, dbB, dbc): $con = Propel::getConnection("dbA"); try { $con->begin(); // update to dbA // update to dbB //update to dbc $con->commit(); } catch (PropelException $e) { $con->rollback(); throw $e; } If dbc fails, I can rollback the dbA but I can't rollback dbb. I think this problem should be database independent. And since I am using ORM, this should be ORM independent as well. Update: Some of the database transactions are wrapped in ORM, some are using naked PDO, oledb ( or whatever bare minimum language provided database calls). So my solution has to take care this. Any idea?

    Read the article

  • Error when trying to use PDO object

    - by Nate
    EDIT: I'm going to put my code in here due to comments below. So sit tight for a few minutes ;-) Thanks! I'm an inexperienced php programmer and only found out about PDO a few days ago. I'm now trying to port my website code over to using PDO, but I am getting an error when I try to use the PDO object that I create. I'm using a CMS on my website, and it makes building page output kind of complicated, but I've tried to draw the structure of what is being called below: index.php -class myClass defined --method myFunction defined (it gets called on pageload & returns the page output) ---include file1.php ----require_once('mysql_connect.php') (creates pdo object) ----*I can use the pdo object here successfully* ----require_once('file2.php') -----require_once('mysql_connect.php') -----function myFunction2 defined ------*trying to use the pdo object here results in the error* The error I'm getting is: Fatal error: Call to a member function prepare() on a non-object in /home/rgcpanel/public_html/account/account_functions.php on line 147 Any idea what's going on?

    Read the article

  • PHP pagination class

    - by Lizard
    I am looking for a php pagination class, I have used a rather simple one in the past and it is no longer supported. I was wondering if anyone had any recommendations ? It seems pointless to build my own when there are probably so many good ones out there. Thanks in advnace!

    Read the article

  • Issue in Creating an Insert Query See Description Below...

    - by Parth
    I am creating a Insert Query using PHP.. By fetching the data from a Audit table and iterating the values of it in loops.. table from which I am fetching the value has the snapshot below: The Code I am using to create is given below: mysql_select_db('information_schema'); $select = mysql_query("SELECT TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA = 'pranav_test'"); $selectclumn = mysql_query("SELECT * FROM COLUMNS WHERE TABLE_SCHEMA = 'pranav_test'"); mysql_select_db('pranav_test'); $seletaudit = mysql_query("SELECT * FROM jos_audittrail WHERE live = 0"); $tables = array(); $i = 0; while($row = mysql_fetch_array($select)) { $tables[$i++] =$row['TABLE_NAME']; } while($row2 = mysql_fetch_array($seletaudit)) { $audit[] =$row2; } foreach($audit as $val) { if($val['operation'] == "INSERT") { if(in_array($val['table_name'],$tables)) { $insert = "INSERT INTO '".$val['table_name']."' ("; $selfld = mysql_query("SELECT field FROM jos_audittrail WHERE table_name = '".$val['table_name']."' AND operation = 'INSERT' AND trackid = '".$val['trackid']."'"); while($row3 = mysql_fetch_array($selfld)) { $values[] = $row3; } foreach($values as $field) { $insert .= "'".$field['field']."', "; } $insert .= "]"; $insert = str_replace(", ]",")",$insert); $insert .= " values ("; $selval = mysql_query("SELECT newvalue FROM jos_audittrail WHERE table_name = '".$val['table_name']."' AND operation = 'INSERT' AND trackid = '".$val['trackid']."' AND live = 0"); while($row4 = mysql_fetch_array($selval)) { $value[] = $row4; } /*echo "<pre>"; print_r($value);exit;*/ foreach($value as $data) { $insert .= "'".$data['newvalue']."', "; } $insert .= "["; $insert = str_replace(", [",")",$insert); } } } When I Echo the $insert out of the most outer for loop (for auditrail) The values get printed as many times as the records are found for the outer for loop..i.e 'orderby= show_noauth= show_title= link_titles= show_intro= show_section= link_section= show_category= link_category= show_author= show_create_date= show_modify_date= show_item_navigation= show_readmore= show_vote= show_icons= show_pdf_icon= show_print_icon= show_email_icon= show_hits= feed_summary= page_title= show_page_title=1 pageclass_sfx= menu_image=-1 secure=0 ', '0000-00-00 00:00:00', '13', '20', '1', '152', 'accmenu', 'IPL', 'ipl', 'index.php?option=com_content&view=archive', 'component' gets repeated , i.e. INSERT INTO 'jos_menu' ('params', 'checked_out_time', 'ordering', 'componentid', 'published', 'id', 'menutype', 'name', 'alias', 'link', 'type', 'params', 'checked_out_time', 'ordering', 'componentid', 'published', 'id', 'menutype', 'name', 'alias', 'link', 'type', 'params', 'checked_out_time', 'ordering', 'componentid', 'published', 'id', 'menutype', 'name', 'alias', 'link', 'type', 'params', 'checked_out_time', 'ordering', 'componentid', 'published', 'id', 'menutype', 'name', 'alias', 'link', 'type', 'params', 'checked_out_time', 'ordering', 'componentid', 'published', 'id', 'menutype', 'name', 'alias', 'link', 'type', 'params', 'checked_out_time', 'ordering', 'componentid', 'published', 'id', 'menutype', 'name', 'alias', 'link', 'type', 'params', 'checked_out_time', 'ordering', 'componentid', 'published', 'id', 'menutype', 'name', 'alias', 'link', 'type', 'params', 'checked_out_time', 'ordering', 'componentid', 'published', 'id', 'menutype', 'name', 'alias', 'link', 'type', 'params', 'checked_out_time', 'ordering', 'componentid', 'published', 'id', 'menutype', 'name', 'alias', 'link', 'type', 'params', 'checked_out_time', 'ordering', 'componentid', 'published', 'id', 'menutype', 'name', 'alias', 'link', 'type', 'params', 'checked_out_time', 'ordering', 'componentid', 'published', 'id', 'menutype', 'name', 'alias', 'link', 'type') values ('orderby= show_noauth= show_title= link_titles= show_intro= show_section= link_section= show_category= link_category= show_author= show_create_date= show_modify_date= show_item_navigation= show_readmore= show_vote= show_icons= show_pdf_icon= show_print_icon= show_email_icon= show_hits= feed_summary= page_title= show_page_title=1 pageclass_sfx= menu_image=-1 secure=0 ', '0000-00-00 00:00:00', '13', '20', '1', '152', 'accmenu', 'IPL', 'ipl', 'index.php?option=com_content&view=archive', 'component', 'orderby= show_noauth= show_title= link_titles= show_intro= show_section= link_section= show_category= link_category= show_author= show_create_date= show_modify_date= show_item_navigation= show_readmore= show_vote= show_icons= show_pdf_icon= show_print_icon= show_email_icon= show_hits= feed_summary= page_title= show_page_title=1 pageclass_sfx= menu_image=-1 secure=0 ', '0000-00-00 00:00:00', '13', '20', '1', '152', 'accmenu', 'IPL', 'ipl', 'index.php?option=com_content&view=archive', 'component', 'orderby= show_noauth= .. .. .. .. and so on What I want is I should get these Values for once, I know there is mistake using the outer Forloop, but I m not getting the idea of rectifying it.. Please help... please poke me for more clarification...

    Read the article

  • How to determine the difference among dates in the same column?

    - by Vera
    How to determine the difference among dates of birth? +----------+----------+------------+ | id | name | birth | +----------+----------+------------+ | 00001 | Claws | 2010-04-17 | | 00002 | Claws | 2010-01-31 | | 00003 | Claws | 2009-11-31 | | 00004 | Claws | 2009-09-31 | | 00005 | Claws | 2009-07-31 | | 00006 | Claws | 2008-10-31 | +----------+----------+------------+ I would like to obtain this: +----------+----------+------------+------------------------------------------+ | id | name | birth | diff | +----------+----------+------------+------------------------------------------+ | 00001 | Claws | 2010-04-17 | diff (id1-id2) = 2010-01-31 - 2010-04-17 | | 00002 | Claws | 2010-01-31 | diff (id2-id3) = 2010-01-31 - 2009-11-31 | | 00003 | Claws | 2009-11-31 | diff (id3-id4) = 2010-01-31 - 2009-09-31 | If possible, ORDER BY diff DESC Thank you for helping Vera

    Read the article

  • Ordering Wordpress posts by most recent comment

    - by James
    I'm wanting to order Wordpress posts by the most recent comment. To the best of my knowledge this isn't possible using the WP_Query object, and would require a custom $wpdb query, which I can easily write. However, I then don't know how to setup the loop to run off this object. Can anyone help?

    Read the article

< Previous Page | 384 385 386 387 388 389 390 391 392 393 394 395  | Next Page >