Search Results

Search found 63877 results on 2556 pages for 'mysql error 1452'.

Page 460/2556 | < Previous Page | 456 457 458 459 460 461 462 463 464 465 466 467  | Next Page >

• Search implementation dilemma: full text vs. plain SQL

  - by Ethan

  I have a MySQL/Rails app that needs search. Here's some info about the data: Users search within their own data only, so searches are narrowed down by user_id to begin with. Each user will have up to about five thousand records (they accumulate over time). I wrote out a typical user's records to a text file. The file size is 2.9 MB. Search has to cover two columns: title and body. title is a varchar(255) column. body is column type text. This will be lightly used. If I average a few searches per second that would be surprising. It's running an a 500 MB CentOS 5 VPS machine. I don't want relevance ranking or any kind of fuzziness. Searches should be for exact strings and reliably return all records containing the string. Simple date order -- newest to oldest. I'm using the InnoDB table type. I'm looking at plain SQL search (through the searchlogic gem) or full text search using Sphinx and the Thinking Sphinx gem. Sphinx is very fast and Thinking Sphinx is cool, but it adds complexity, a daemon to maintain, cron jobs to maintain the index. Can I get away with plain SQL search for a small scale app?

  Read the article

• How to join a table in symfony (Propel) and retrieve object from both table with one query

  - by Jean-Philippe

  Hi, I'm trying to get an easy way to fetch data from two joined Mysql table using Propel (inside Symfony) but in one query. Let's say I do this simple thing: $comment = CommentPeer::RetrieveByPk(1); print $comment->getArticle()->getTitle(); //Assuming the Article table is joined to the Comment table Symfony will call 2 queries to get that done. The first one to get the Comment row and the next one to get the Article row linked to the comment one. Now, I am trying to find a way to make all that within one query. I've tried to join them using $c = new Criteria(); $c->addJoin(CommentPeer::ARTICLE_ID, ArticlePeer::ID); $c->add(CommentPeer::ID, 1); $comment = CommentPeer::doSelectOne($c); But when I try to get the Article object using $comment->getArticle() It will still issue the query to get the Article row. I could easily clear all the selected columns and select the columns I need but that would not give me the Propel object I'd like, just an array of the query's raw result. So how can I get a populated propel object of two (or more) joined table with only one query? Thanks, JP

  Read the article

• Algorithm To Select Most Popular Places from Database

  - by Russell C.

  We have a website that contains a database of places. For each place our users are able to take one of the follow actions which we record: VIEW - View it's profile RATING - Rate it on a scale of 1-5 stars REVIEW - Review it COMPLETED - Mark that they've been there WISH LIST - Mark that they want to go there FAVORITE - Mark that it's one of their favorites In our database table of places each place contains a count of the number of times each action above was taken as well as the average rating given by users. views ratings avg_rating completed wishlist favorite What we want to be able to do is generate lists of the top places using the above information. Ideally, we would want to be able to generate this list using a relatively simple SQL query without needing to do any legwork to calculate additional fields or stack rank places against one another. That being said, since we only have about 50,000 places we could run a nightly cron job to calculate some fields such as rankings on different categories if it would make a meaningful difference in the overall results of our top places. I'd appreciate if you could make some suggestions on how we should think about bubbling the best places to the top, which criteria we should weight more heavily, and given that information - suggest what the MySQL query would need to look like in order to select the top 10 places. One thing to note is that at this time we are less concerned with the recency of a place being popular - meaning that looking at the aggregate information is fine and that more recent data doesn't need to be weighted more heavily. Thanks in advance for your help & advice!

  Read the article

• Unknown date shown for mails received from Mantis in Webmail

  - by Timw

  Hello, Im using Mantis bug tracker v1.1.8, and the Horde Webmail System for my company emails. When emails sent by Mantis arrive in my company inbox, i get Unknown Date in the Date field of the inbox view. When i open the message, i see the Date like Thu, 31 Dec 2009 14:32:15 +0580. The other mails, whose date i can see in the Inbox view have date in a format like Mon, 21 Dec 2009 06:56:18 +0100 [12/21/2009 11:26:18 AM IST] . For your reference i have pasted below the contents of my config_inc.php <?php $g_hostname = 'localhost'; $g_db_type = 'mysql'; $g_database_name = 'bugtracker_mantis'; $g_db_username = 'root'; $g_db_password = ''; # select the method to mail by: # 0 - mail() # 1 - sendmail # 2 - SMTP $g_phpMailer_method = 2; # This option allows you to use a remote SMTP host. Must use the phpMailer script # Name of smtp host, needed for phpMailer, taken from php.ini $g_smtp_host = <my_smtp_host>; $g_administrator_email = <my_administrator_email>; $g_webmaster_email = <my_webmaster_email>; $g_from_email = <my_from_email>; putenv("TZ=Asia/Calcutta"); #Date Settings $g_default_language = 'english'; $g_short_date_format = 'dm-Y'; $g_normal_date_format = 'dmY H: i'; $g_complete_date_format = 'm-d-y H:i T'; ?> Any way to fix this problem ? Thank You

  Read the article

• Problems with utf-8 encoding in php

  - by Addsy

  Hi, Another utf-8 related problem I believe... I am using php to update data in a mysql db then display that data elsewhere in the site. Previously I have run into utf-8 problems before where special characters are displayed as question marks when viewed in a browser but this one seems slightly different. I have a number of records to enter that contain the è character. If I enter this directly in the db then it appears correctly on the page so I take this to mean that utf-8 content is being output correctly. However when I try and update the values in the db through php, then the è character is replaced. What appears instead is & Atilde ; & uml ; (without the spaces) which appears in the browser as è I have the tables in the database set to use UTF-8. I believe this is correct cos, as mentioned, if I update the db through phpMyAdmin, its all ok. Similarly I have set the character encoding for the page which seems to be correct. I am also running the sql statement "SET NAMES 'utf8';" before trying to update the db. Anyone have any other ideas as to where the problem may lie? Many thanks

  Read the article

• Find all records in database that are within a certain distance of a set of lat and long points

  - by Mike L

  I've seen all the examples and here's what I got so far. my table is simple: schools (table name) - School_ID - lat - long - county - extrainfo here's my code: <?php $con = mysql_connect("xxx","xxx","xxx"); if (!$con) { die('Could not connect: ' . mysql_error()); } else {} mysql_select_db("xxx", $con); $latitude = "36.265541"; $longitude = "-119.207153"; $distance = "1"; //miles $qry = "SELECT *, (3958.75 * ACOS(SIN(" . $latitude . " / 57.2958)*SIN(lat / 57.2958)+COS(" . $latitude . " / 57.2958)*COS(lat / 57.2958)*COS(long / 57.2958 - " . $longitude . " / 57.2958))) as distance FROM schools WHERE (3958.75 * ACOS(SIN(" . $latitude . " / 57.2958)*SIN(lat / 57.2958)+COS(" . $latitude . " / 57.2958)*COS(lat / 57.2958)*COS(long / 57.2958 - " . $longitude . " / 57.2958))) <= " . $distance; $results = mysql_query($qry); if (mysql_num_rows($results) > 0) { while($row = mysql_fetch_assoc($results)) { print_r($row); } } else {} mysql_close($con); ?> but I get this error when I try to run it: Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource

  Read the article

• Is there any other efficient way to use table variable instead of using temporary table

  - by varta shrimali

  we are writing script to display banners on a web page where we are using temporary table in mysql procedure. Is there any other efficient way to use table variable instead of using temporary table we are using following code: -- banner location CURSOR -- DECLARE banner_location_cursor CURSOR FOR select bm.id as masterId, bm.section as masterName, bs.id as locationId, bs.sectionName as locationName from banner_master as bm inner join banner_section as bs on bm.id=bs.masterId where bm.section=sCode ; -- DECLARE banner CURSORS DECLARE banner_cursor CURSOR FOR SELECT bd.id as bannerId, bd.sectionId, bd.bannerName, bd.websiteURL, bd.paymentType, bd.status, bd.startDate, bd.endDate, bd.bannerDisplayed, bs.id, bs.sectionName from banner_detail as bd inner join banner_section as bs on bs.id=bd.sectionId where bs.id= location_id and bd.status='A' and (dates between cast(bd.startDate as DATE) and cast(bd.endDate as DATE)) order by rand(), bd.bannerDisplayed asc limit 1 ; DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = 1; SET dates = (select curdate()); -- RESULTS TABLE WHICH WILL BE RETURNED -- CREATE temporary TABLE test ( b_id INT, s_id INT, b_name varchar(128), w_url varchar(128), p_type varchar(128), st char(1), s_date datetime, e_date datetime, b_display int, sec_id int, s_name varchar(128) ); -- OPEN banner location CURSOR OPEN banner_location_cursor; the_loop: LOOP FETCH banner_location_cursor INTO master_id, master_name, location_id, location_name; IF no_more_rows THEN CLOSE banner_location_cursor; leave the_loop; END IF; OPEN banner_cursor; -- select FOUND_ROWS(); the_loop2: LOOP FETCH banner_cursor INTO banner_id, section_id, banner_name, website_url, payment, status, start_date, end_date, banner_displayed, sec_id, section_name; IF no_more_rows THEN set no_more_rows = 0; CLOSE banner_cursor; leave the_loop2; END IF; INSERT INTO test ( b_id, s_id, b_name , w_url, p_type, st, s_date, e_date, b_display, sec_id, s_name ) VALUES ( banner_id, section_id, banner_name, website_url, payment, status, start_date, end_date, banner_displayed, sec_id, section_name ); UPDATE banner_detail set bannerDisplayed = (banner_displayed+1) where id = banner_id; END LOOP the_loop2; END LOOP the_loop; -- RETURN result SELECT * FROM test; -- DROP RESULTS TABLE DROP TABLE test; END

  Read the article

• sending email in php unable to use HTML tags

  - by JPro

  I am trying to send an email through the result sets generated in MySQL in PHP This is the code. <?php $txtMsg = '<table><th>Name</th>'; $txtMsg = ''; mysql_connect("localhost", "root", "pop") or die(mysql_error()); mysql_select_db("jpd") or die(mysql_error()); $oustanding = mysql_query("select Name from results") or die(mysql_error()); $num=mysql_num_rows($oustanding); while($row1 = mysql_fetch_array( $oustanding )) { ?> <tr> <td><h3><?php echo $row1['Name']; ?></h3></td> </tr> <?php $txtMsg .= "<tr><td>".$row1['Name']."</td></tr>"; } ini_set ( "SMTP", "xy.domain.com" ); $mail_to= '[email protected]'; $mail_from='[email protected]'; $mail_sub='OutStanding Results'; $mail_mesg=$txtMsg; //Check for success/failure of delivery if(mail($mail_to,$mail_sub,$mail_mesg,"From: $mail_from")) echo "<br><br>Email Successfully Sent!"; else echo "<br><br>Error Sending Email!"; } ?> The problem is , I want the results to be displayed in table format. But instead of processing the html tags, they are getting printed as well. How to get the email with table format? Thanks.

  Read the article

• Facebook App : Problem with storing user info

  - by Jack

  I want to store basic user info like name and proxied email into my MySQL database. Here's my code $user = $facebook->require_login($required_permissions = 'publish_stream','status_update','email'); $con = mysql_connect("xxxx","xxxxx","xxxxx"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("xxx_userdb", $con); //Uptil Here it is working fine...No problem with db connection $fql_firstname = "select first_name from user where uid = '$user'"; $fql_lastname = "select last_name from user where uid = '$user'"; $fql_email = "select proxied_email from user where uid = '$user'"; $fql_fname_result = $facebook->api_client->fql_query($fql_firstname); $fql_lname_result = $facebook->api_client->fql_query($fql_lastname); $fql_email_result = $facebook->api_client->fql_query($fql_email); echo "<pre>FQL Result:" . print_r($fql_fname_result,true) . "</pre>"; echo "<pre>FQL Result:" . print_r($fql_lname_result,true) . "</pre>"; echo "<pre>FQL Result:" . print_r($fql_email_result,true) . "</pre>"; Here's the output I get FQL Result: FQL Result: FQL Result: What am I doing wrong?

  Read the article

• Form Validation using Java inside PHP

  - by Mikey1980

  I have a simple problem but no matter what I try I can't see to get it to work. I have a form on a php page and I need to validate the qty value on my form so that it doesn't exceed $qty (value pulled from mySQL) and is not less than zero. Sounds easy--hmm wish it were..lol! I had it checking if the value was numeric and in my attempts to make this work I even broke that--not a good morning..lol! Here's a snip of my Java Fn: <script type='text/javascript'> function checkQty(elem){ var numericExpression = /^[0-9]+$/; if(elem.value.match(numericExpression)){ return true; }else{ alert("Quantity for RMA must be greater than zero and less than original order!"); elem.focus(); return false; } } </script> The function is called from the submit button, onClick: <input type="submit" name="submit" onclick="checkQty(document.getElementById('qty')";"> I've tried: var numericExpression = /^[0-9]+$/; if(elem.value.match(numericExpression) || elem.value < 0 || elem.value > <? int($qty) ?>){ No dice....HELP!?!

  Read the article

• How to combine a Distance and Keyword SQL query?

  - by Jason

  Hi Folks, I have a tables in my database called "points" and "category". A user will input info into both a location input and a keyword input text box. Then I want to find points in my table where the keyword matches either the "title" field in the points table, or the "category" but are within a certain distance from the user's location. I want to order the results by distance. Here are the 2 queries which btoh work independently: $mysql = "SELECT *, ( 3959 * acos( cos( radians('$search_lat') ) * cos( radians( lat ) ) * cos( radians( longi ) - radians('$search_lng') ) + sin( radians('$search_lat') ) * sin( radians( lat ) ) ) ) AS distance FROM points HAVING distance < '$radius'"; $mysql2 = "SELECT * FROM `points` LEFT JOIN category USING ( category_id ) WHERE (point_title LIKE '%$esc_catsearch%' OR category.title LIKE '%$esc_catsearch%')"; Here is what I tried: $sql_search = sprintf("SELECT *,point_id FROM points WHERE point_title LIKE '%%%s%%' UNION SELECT *, ( 3959 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( longi ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance FROM points HAVING distance < '%s' ORDER BY distance LIMIT %d , %d", $esc_catsearch, mysql_real_escape_string($search_lat), mysql_real_escape_string($search_lng), mysql_real_escape_string($search_lat), mysql_real_escape_string($radius), $offset, $rowsPerPage); But it tells me there is no know column "distance". If I remove the "Order By" phrase then it works but I'm still not sure this is giving me the results I want. I also tried the query the other way around with the distance search first but that seems to ignore my keyword. Any thoughts would be much appreciated!

  Read the article

• Join Query Not Working

  - by John

  Hello, I am using three MySQl tables: comment commentid loginid submissionid comment datecommented login loginid username password email actcode disabled activated created points submission submissionid loginid title url displayurl datesubmitted In these three tables, the "loginid" correspond. I would like to pull the top 10 loginids based on the number of "submissionid"s. I would like to display them in a 3-column HTML table that shows the "username" in the first column, the number of "submissionid"s in the second column, and the number of "commentid"s in the third column. I tried using the query below but it did not work. Any idea why not? Thanks in advance, John $sqlStr = "SELECT l.username ,l.loginid ,c.commentid ,count(s.commentid) countComments ,c.comment ,c.datecommented ,s.submissionid ,count(s.submissionid) countSubmissions ,s.title ,s.url ,s.displayurl ,s.datesubmitted FROM comment AS c INNER JOIN login AS l ON c.loginid = l.loginid INNER JOIN submission AS s ON c.loginid = s.loginid GROUP BY c.loginid ORDER BY countSubmissions DESC LIMIT 10"; $result = mysql_query($sqlStr); $arr = array(); echo "<table class=\"samplesrec1\">"; while ($row = mysql_fetch_array($result)) { echo '<tr>'; echo '<td class="sitename1"><a href="http://www...com/.../members/index.php?profile='.$row["username"].'">'.stripslashes($row["username"]).'</a></td>'; echo '</tr>'; echo '<td class="sitename1">'.stripslashes($row["countSubmissions"]).'</td>'; echo '</tr>'; echo '</tr>'; echo '<td class="sitename1">'.stripslashes($row["countComments"]).'</td>'; echo '</tr>'; } echo "</table>";

  Read the article

• LOAD DATA INFILE with variables

  - by Hasitha

  I was tring to use the LOAD DATA INFILE as a sotred procedure but it seems it cannot be done. Then i tried the usual way of embedding the code to the application itself like so, conn = new MySqlConnection(connStr); conn.Open(); MySqlCommand cmd = new MySqlCommand(); cmd = conn.CreateCommand(); string tableName = getTableName(serverName); string query = "LOAD DATA INFILE '" + fileName + "'INTO TABLE "+ tableName +" FIELDS TERMINATED BY '"+colSep+"' ENCLOSED BY '"+colEncap+"' ESCAPED BY '"+colEncap+"'LINES TERMINATED BY '"+colNewLine+"' ("+colUpFormat+");"; cmd.CommandText = query; cmd.ExecuteNonQuery(); conn.Close(); The generated query that gets saved in the string variable query is, LOAD DATA INFILE 'data_file.csv'INTO TABLE tbl_shadowserver_bot_geo FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '"'LINES TERMINATED BY '\n' (timestamp,ip,port,asn,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,url,agent,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy); But now when i run the program it gives an error saying, MySQLException(0x80004005) Parameter @dummy must be defined first. I dont know how to get around this, but when i use the same query on mysql itself directly it works fine. PLEASE help me..thank you very much :)

  Read the article

• Optimal two variable linear regression SQL statement (censoring outliers)

  - by Dave Jarvis

  Problem Am looking to apply the y = mx + b equation (where m is SLOPE, b is INTERCEPT) to a data set, which is retrieved as shown in the SQL code. The values from the (MySQL) query are: SLOPE = 0.0276653965651912 INTERCEPT = -57.2338357550468 SQL Code SELECT ((sum(t.YEAR) * sum(t.AMOUNT)) - (count(1) * sum(t.YEAR * t.AMOUNT))) / (power(sum(t.YEAR), 2) - count(1) * sum(power(t.YEAR, 2))) as SLOPE, ((sum( t.YEAR ) * sum( t.YEAR * t.AMOUNT )) - (sum( t.AMOUNT ) * sum(power(t.YEAR, 2)))) / (power(sum(t.YEAR), 2) - count(1) * sum(power(t.YEAR, 2))) as INTERCEPT FROM (SELECT D.AMOUNT, Y.YEAR FROM CITY C, STATION S, YEAR_REF Y, MONTH_REF M, DAILY D WHERE -- For a specific city ... -- C.ID = 8590 AND -- Find all the stations within a 15 unit radius ... -- SQRT( POW( C.LATITUDE - S.LATITUDE, 2 ) + POW( C.LONGITUDE - S.LONGITUDE, 2 ) ) <15 AND -- Gather all known years for that station ... -- S.STATION_DISTRICT_ID = Y.STATION_DISTRICT_ID AND -- The data before 1900 is shaky; insufficient after 2009. -- Y.YEAR BETWEEN 1900 AND 2009 AND -- Filtered by all known months ... -- M.YEAR_REF_ID = Y.ID AND -- Whittled down by category ... -- M.CATEGORY_ID = '001' AND -- Into the valid daily climate data. -- M.ID = D.MONTH_REF_ID AND D.DAILY_FLAG_ID <> 'M' GROUP BY Y.YEAR ORDER BY Y.YEAR ) t Data The data is visualized here (with five outliers highlighted): Questions How do I return the y value against all rows without repeating the same query to collect and collate the data? That is, how do I "reuse" the list of t values? How would you change the query to eliminate outliers (at an 85% confidence interval)? The following results (to calculate the start and end points of the line) appear incorrect. Why are the results off by ~10 degrees (e.g., outliers skewing the data)? (1900 * 0.0276653965651912) + (-57.2338357550468) = -4.66958228 (2009 * 0.0276653965651912) + (-57.2338357550468) = -1.65405406 I would have expected the 1900 result to be around 10 (not -4.67) and the 2009 result to be around 11.50 (not -1.65). Thank you!

  Read the article

• Foosball result prediction

  - by Wolf

  In our office, we regularly enjoy some rounds of foosball / table football after work. I have put together a small java program that generates random 2vs2 lineups from the available players and stores the match results in a database afterwards. The current prediction of the outcome uses a simple average of all previous match results from the 4 involved players. This gives a very rough estimation, but I'd like to replace it with something more sophisticated, taking into account things like: players may be good playing as attacker but bad as defender (or vice versa) players do well against a specific opponent / bad against others some teams work well together, others don't skills change over time What would be the best algorithm to predict the game outcome as accurately as possible? Someone suggested using a neural network for this, which sounds quite interesting... but I do not have enough knowledge on the topic to say if that could work, and I also suspect it might take too many games to be reasonably trained. EDIT: Had to take a longer break from this due to some project deadlines. To make the question more specific: Given the following mysql table containing all matches played so far: table match_result match_id int pk match_start datetime duration int (match length in seconds) blue_defense int fk to table player blue_attack int fk to table player red_defense int fk to table player red_attack int fk to table player score_blue int score_red int How would you write a function predictResult(blueDef, blueAtk, redDef, redAtk) {...} to estimate the outcome as closely as possible, executing any sql, doing calculations or using external libraries?

  Read the article

• database schema eligible for delta synchronization

  - by WilliamLou

  it's a question for discussion only. Right now, I need to re-design a mysql database table. Basically, this table contains all the contract records I synchronized from another database. The contract record can be modified, deleted or users can add new contract records via GUI interface. At this stage, the table structure is exactly the same as the Contract info (column: serial number, expiry date etc.). In that case, I can only synchronize the whole table (delete all old records, replace with new ones). If I want to delta(only synchronize with modified, new, deleted records) synchronize the table, how should I change the database schema? here is the method I come up with, but I need your suggestions because I think it's a common scenario in database applications. 1)introduce a sequence number concept/column: for each sequence, mark the new added records, modified records, deleted records with this sequence number. By recording the last synchronized sequence number, only pass those records with higher sequence number; 2) because deleted contracts can be added back, and the original table has primary key constraints, should I create another table for those deleted records? or add a flag column to indicate if this contract has been deleted? I hope I explain my question clearly. Anyway, if you know any articles or your own suggestions about this, please let me know. Thanks!

  Read the article

• Using Constraints on Hierarchical Data in a Self-Referential Table

  - by pbarney

  Suppose you have the following table, intended to represent hierarchical data: +--------+-------------+ | Field | Type | +--------+-------------+ | id | int(10) | | parent | int(10) | | name | varchar(45) | +--------+-------------+ The table is self-referential in that the parent_id refers to id. So you might have the following data: +----+--------+---------------+ | id | parent | name | +----+--------+---------------+ | 1 | 0 | fruit | | 2 | 0 | vegetable | | 3 | 1 | apple | | 4 | 1 | orange | | 5 | 3 | red delicious | | 6 | 3 | granny smith | | 7 | 3 | gala | +----+--------+---------------+ Using MySQL, I am trying to impose a (self-referential) foreign key constraint upon the data to update on cascades and prevent deletion of fruit if they have "children." So I used the following: CREATE TABLE `idtlp_main`.`fruit` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `parent` INT(10) UNSIGNED, `name` VARCHAR(45) NOT NULL, PRIMARY KEY (`id`), CONSTRAINT `fk_parent` FOREIGN KEY (`parent`) REFERENCES `fruit` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT ) ENGINE = InnoDB; From what I understand, this should fit my requirements. (And parent must default to null to allow insertions, correct?) The problem is, if I change the id of a record, it will not cascade: Cannot delete or update a parent row: a foreign key constraint fails (`iddoc_main`.`fruit`, CONSTRAINT `fk_parent` FOREIGN KEY (`parent`) REFERENCES `fruit` (`id`) ON UPDATE CASCADE) What am I missing? Feel free to correct me if my terminology is screwed up... I'm new to constraints.

  Read the article

• How to restrict text search to a certain subset of the database ?

  - by Nikhil Garg

  I have a large central database of around 1 million heavy records. In my app, for every user I would have a subset of rows from central table, which would be very small (probably 100 records each).When a particular user has logged in , I would want to search on this data set only. Example: Say I have a central database of all cars in the world. I have a user profile for General Motors(GM) , Ferrari etc. When GM is logged in I just want to search(a full text search and not fire a sql query) for those cars which are manufactured by GM. Also GM may launch/withdraw a model in which case central db would be updated & so would be rowset associated with GM. In case of acquisitions, db of certain profiles may change without launch/removal of new car. So central db wont change then , but rowsets may. Whats the best way to implement such a design ? These smaller row sets would need to be dynamic depending on user activities. We are on Rails 2.3.5 and use thinking_sphinx as the connector and Sphinx/MySQL for search and relational associations.

  Read the article

• Is there anyway to carry a value in php forward to a second page?

  - by Henry Aspden

  I have created a php site, and previously it was listing only products with defined values. I have now changed it to include an array of products for example all products WHERE id = "spotlights" and this works great so it means I can add new products just to the database, but I still have to add the second page manually. e.g going from the product div on the main page, through to www.example.com/spotlight_1.php Is there anyway in PHP to carry the data from my index.php e.g. the ID through to the next page? so that I can have a template product.php page, and I can use a database pull to echo the product information required. So on index.php i click on the product with ID="1" and on the product.php page, it loads the relevant data for product 1. I can write the php SQL/mySQL calls myself, its just the way to carry accross a value from the previous page which I dont understand Regards Henry p.s. all the IDs and things are stored in the database already as 1 to 3digit values e.g. 3 or or 93 or 254 Any advice as always is greatly appreciated Regards Henry

  Read the article

• Complex SQL query, one to many relationship

  - by Ethan

  Hey SO, I have a query such that I need to get A specific dog All comments relating to that dog The user who posted each comment All links to images of the dog the user who posted each link I've tried a several things, and can't figure out quite how to work it. Here's what I have (condensed so you don't have to wade through it all): SELECT s.dog_id, s.name, c.comment, c.date_added AS comment_date_added, u.username AS comment_username, u.user_id AS comment_user_id, l.link AS link, l.date_added AS link_date_added, u2.username AS link_username, u2.user_id AS link_user_id FROM dogs AS d LEFT JOIN comments AS c ON c.dog_id = d.dog_id LEFT JOIN users AS u ON c.user_id = u.user_id LEFT JOIN links AS l ON l.dog_id = d.dog_id LEFT JOIN users AS u2 ON l.user_id = u2.user_id WHERE d.dog_id = '1' It's sorta close to working, but it'll only return me the first comment, and the first link all as one big array with all the info i requested. The are multiple comments and links per dog, so I need it to give me all the comments and all the links. Ideally it'd return an object with dog_id, name, comments(an array of the comments), links(an array of the links) and then comments would have a bunch of comments, date_added, username, and user_id and links would have a bunch of links with link, date_added, username and user_id. It's got to work even if there are no links or comments. I learned the basics of mySQL somewhat recently, but this is pretty far over my head. Any help would be wonderful. Thanks!

  Read the article

• memory leak in php script

  - by Jasper De Bruijn

  Hi, I have a php script that runs a mysql query, then loops the result, and in that loop also runs several queries: $sqlstr = "SELECT * FROM user_pred WHERE uprType != 2 AND uprTurn=$turn ORDER BY uprUserTeamIdFK"; $utmres = mysql_query($sqlstr) or trigger_error($termerror = __FILE__." - ".__LINE__.": ".mysql_error()); while($utmrow = mysql_fetch_array($utmres, MYSQL_ASSOC)) { // some stuff happens here // echo memory_get_usage() . " - 1241<br/>\n"; $sqlstr = "UPDATE user_roundscores SET ursUpdDate=NOW(),ursScore=$score WHERE ursUserTeamIdFK=$userteamid"; if(!mysql_query($sqlstr)) { $err_crit++; $cLog->WriteLogFile("Failed to UPDATE user_roundscores record for user $userid - teamuserid: $userteamid\n"); echo "Failed to UPDATE user_roundscores record for user $userid - teamuserid: $userteamid<br>\n"; break; } unset($sqlstr); // echo memory_get_usage() . " - 1253<br/>\n"; // some stuff happens here too } The update query never fails. For some reason, between the two calls of memory_get_usage, there is some memory added. Because the big loop runs about 500.000 or more times, in the end it really adds up to alot of memory. Is there anything I'm missing here? could it herhaps be that the memory is not actually added between the two calls, but at another point in the script? Edit: some extra info: Before the loop it's at about 5mb, after the loop about 440mb, and every update query adds about 250 bytes. (the rest of the memory gets added at other places in the loop). The reason I didn't post more of the "other stuff" is because its about 300 lines of code. I posted this part because it looks to be where the most memory is added.

  Read the article

• Optimal two variable linear regression SQL statement

  - by Dave Jarvis

  Problem Am looking to apply the y = mx + b equation (where m is SLOPE, b is INTERCEPT) to a data set, which is retrieved as shown in the SQL code. The values from the (MySQL) query are: SLOPE = 0.0276653965651912 INTERCEPT = -57.2338357550468 SQL Code SELECT ((sum(t.YEAR) * sum(t.AMOUNT)) - (count(1) * sum(t.YEAR * t.AMOUNT))) / (power(sum(t.YEAR), 2) - count(1) * sum(power(t.YEAR, 2))) as SLOPE, ((sum( t.YEAR ) * sum( t.YEAR * t.AMOUNT )) - (sum( t.AMOUNT ) * sum(power(t.YEAR, 2)))) / (power(sum(t.YEAR), 2) - count(1) * sum(power(t.YEAR, 2))) as INTERCEPT FROM (SELECT D.AMOUNT, Y.YEAR FROM CITY C, STATION S, YEAR_REF Y, MONTH_REF M, DAILY D WHERE -- For a specific city ... -- C.ID = 8590 AND -- Find all the stations within a 5 unit radius ... -- SQRT( POW( C.LATITUDE - S.LATITUDE, 2 ) + POW( C.LONGITUDE - S.LONGITUDE, 2 ) ) <15 AND -- Gather all known years for that station ... -- S.STATION_DISTRICT_ID = Y.STATION_DISTRICT_ID AND -- The data before 1900 is shaky; and insufficient after 2009. -- Y.YEAR BETWEEN 1900 AND 2009 AND -- Filtered by all known months ... -- M.YEAR_REF_ID = Y.ID AND -- Whittled down by category ... -- M.CATEGORY_ID = '001' AND -- Into the valid daily climate data. -- M.ID = D.MONTH_REF_ID AND D.DAILY_FLAG_ID <> 'M' GROUP BY Y.YEAR ORDER BY Y.YEAR ) t Data The data is visualized here: Questions How do I return the y value against all rows without repeating the same query to collect and collate the data? That is, how do I "reuse" the list of t values? How would you change the query to eliminate outliers (at an 85% confidence interval)? The following results (to calculate the start and end points of the line) appear incorrect. Why are the results off by ~10 degrees (e.g., outliers skewing the data)? (1900 * 0.0276653965651912) + (-57.2338357550468) = -4.66958228 (2009 * 0.0276653965651912) + (-57.2338357550468) = -1.65405406 I would have expected the 1900 result to be around 10 (not -4.67) and the 2009 result to be around 11.50 (not -1.65). Thank you!

  Read the article

• Web page database query optimization

  - by morpheous

  I am putting together a web page which is quite 'expensive' in terms of database hits. I don't want to start optimizing at this stage - though with me trying to hit a deadline, I may end up not optimizing at all. Currently the page requires 18 (that's right eighteen) hits to the db. I am already using joins, and some of the queries are UNIONed to minimize the trips to the db. My local dev machine can handle this (page is not slow) however, I feel if I release this into the wild, the number of queries will quickly overwhelm my database (MySQL). I could always use memcache or something similar, but I would much rather continue with my other dev work that needs to be completed before the deadline - at least retrieving the page works - its simply a matter of optimization now (if required). My question therefore is - is 18 db queries for a single page retrieval completely outrageous - (i.e. I should put everything on hold and optimize the hell of the retrieval logic), or shall I continue as normal, meet the deadline and release on schedule and see what happens? [Edit] Just to clarify, I have already done the 'obvious' things like using (single and composite) indexes for fields used in the queries. What I haven't yet done is to run a query analyzer to see if my indexes etc are optimal.

  Read the article

• JSON VIEW using GROUP_CONCAT question

  - by Dan Beam

  Hey DBAs and overall smart dudes. I have a question for you. We use MySQL VIEWs to format our data as JSON when it's returned (as a BLOB), which is convenient (though not particularly nice on performance, but we already know this). But, I can't seem to get a particular query working right now (each row contains NULL when it should contain a created JSON object with the values of multiple JOINs). Here's the general idea: SELECT CONCAT( "{", "\"some_list\":[", GROUP_CONCAT( DISTINCT t1.id ), "],", "\"other_list\":[", GROUP_CONCAT( DISTINCT t2.id ), "],", "}" ) cool_json FROM table_name tn INNER JOIN ( some_table st ) ON st.some_id = tn.id LEFT JOIN ( another_table at, another_one ao, used_multiple_times t1 ) ON st.id = at.some_id AND at.different_id = ao.different_id AND ao.different_id = t1.id LEFT JOIN ( another_table2 at2, another_one2 ao2, used_multiple_times t2 ) ON st.id = at2.some_id AND at2.different_id = ao2.different_id AND ao2.different_id = t2.id GROUP BY tn.id ORDER BY tn.name Anybody know the problem here? Am I missing something I should be grouping by? It was working when I was only doing 1 LEFT JOIN & GROUP_CONCAT, but now with multiple JOINs / GROUP_CONCATs it's messing it up. When I move the GROUP_CONCATs from the "cool_json" field they work as expected, but I'd like my data formatted as JSON so I can decode it server-side or client-side in one step.

  Read the article

• User Friendly Video Review with Locking

  - by James Cori

  We have a jquery/php/mysql system that allows a user to log in and review videos built by a system for online viewing. When a user begins reviewing a video, the video is marked as such. But now we've cornered ourselves into the classic browser-based application problem of the user navigating away or closing the browser without completing review. That video would then enter a state of limbo of constantly being reviewed, but never completed, and never re-entering the queue. Options we have are: Build a service (which we already have others) to find review sessions that are outside a duration boundary and reset them back into the queue. Reset review sessions outside a duration boundary when that user logs in. Essentially, if a user locks out a video for review, it'll be unlocked the next time they log in. A suggestion made to me was to use the php/apache session length and on expiration, reset any pending review jobs. I don't even know where to look to implement this as this is one project on a shared server, so it shouldn't be an apache config, but the reset mechanism would need to know the database credentials to be able to reset it... The worst solution everyone hates is preventing the user from navigating away with javascript, asking "Are you sure?!" This system is used by a few hired reviewers, so I'm not exactly dealing with the public here, but I can't prevent users from sharing logins for speedier review, which would knock out the 2nd option above because it would unlock a video being reviewed by someone else using the same login.

  Read the article

< Previous Page | 456 457 458 459 460 461 462 463 464 465 466 467  | Next Page >