Search Results

Search found 14874 results on 595 pages for 'mysql connector'.

Page 139/595 | < Previous Page | 135 136 137 138 139 140 141 142 143 144 145 146  | Next Page >

• MySQL Rating system (calculating average from two tables).

  - by MussuR

  I have two tables, videos and videos_ratings. The videos table has an int videoid field (and many others but those fields are not important I think) and many records. The videos_ratings table has 3 int fields: videoid, rating, rated_by which has many records (multiple records for each fields from the videos table) but not for all records from the videos table. Currently I have the following mysql query: SELECT `videos`.*, avg(`videos_ratings`.`vote`) FROM `videos`, `videos_ratings` WHERE `videos_ratings`.`videoid` = `videos`.`videoid` GROUP BY `videos_ratings`.`videoid` ORDER BY RAND() LIMIT 0, 12 It selects all the records from table videos that have a rating in table video_ratings and calculates the average correctly. But what I need is to select all records from the videos table, no matter if there is a rating for that record or not. And if there aren't any records in the videos_ratings table for that particular videos record, the average function should show 0. Hope someone could understand what I want... :) Thanks!

  Read the article

• how to select and group mysql data based on the follwoing table

  - by user1151680

  how can I achieve the desired result in mysql if my table looks like this. result|year 1 |2011 2 |2011 1 |2011 0 |2011 1 |2012 2 |2012 1 = Won, 2 = lost, 0 = draw Every year can have multiple values like this. Not sure how I can get the desired result like below. year won lost draw totalPlayed 2011 2 1 1 3 2012 1 1 0 2 I have tried the following query but does not get the desired result select year, league_types.league_name, sum(if(result = 1,1,0)) as won, sum(if(result = 0,1,0)) as draw, sum(if(result = 4,1,0)) as noResult, sum(if(result = 2,1,0)) as lost, sum(if(result = 3,1,0)) as tied, sum(if(result > 0 and result < 4,1,0)) as played from match_score_card inner join fixtures on match_score_card.match_id = fixtures.match_id inner join league_types on fixtures.league_id = league_types.league_id where team_id = 1 group by year order by year desc

  Read the article

• MYSQL -Incorrect Syntax

  - by user1854392

  WHILE x > 1 DO SET x = x - 1; SET totalTime = SELECT CONCAT(FLOOR(HOUR(TIMEDIFF(end_time,start_time)) / 24), ' days ', MOD(HOUR(TIMEDIFF(end_time,start_time)), 24), ' hrs ', MINUTE(TIMEDIFF(end_time,start_time)), ' minutes ') AS total_Time I don't see why I am having a syntax error? It is part of a bigger procedure but is pointing to this aas being incorrect Error message: SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT CONCAT(FLOOR(HOUR(TIMEDIFF(end_time,start_time)) / 24,' days',' at line 11 and totalTime is declared as a VARCHAR(50)

  Read the article

• MySQL storage engine dilemma

  - by burntblark

  There are two MySQL database features that I want to use in my application. The first is FULL-TEXT-SEARCH and TRANSACTIONS. Now, the dilemma here is that I cannot get this feature in one storage engine. It's either I use MyIsam (which has the FULL-TEXT-SEARCH feature) or I use InnoDB (which supports the TRANSACTION feature). I can't have both. My question is, is there anyway I can have both features in my application before I am forced to make a choice between the two storage engines.

  Read the article

• dates when saving to mysql database

  - by Patrick

  in my php code I was asking the user to choose day, month and year from some dropdown fields where values where 1, 2, 3 etc instead of 01, 02, 03. these were then combined to form a string like "YYYY-MM-DD" for the insertion in a db (in a date field). Having missed the initial 0, I thought I was sending strings in the wrong format, eg "YYYY-M-D" or YYYY-MM-D", but then I've noticed they appear in the right format in the database anyway: even if I submitted YYYY-M-D, it appeared as YYYY-MM-DD. is this the normal behaviour of mysql? if so, can i just avoid worrying about changing the code in my application?

  Read the article

• MySQL Full-text Search Workaround for innoDB tables

  - by Rob

  I'm designing an internal web application that uses MySQL as its backend database. The integrity of the data is crucial, so I am using the innoDB engine for its foreign key constraint features. I want to do a full-text search of one type of records, and that is not supported natively with innoDB tables. I'm not willing to move to MyISAM tables due to their lack of foreign key support and due to the fact that their locking is per table, not per row. Would it be bad practice to create a mirrored table of the records I need to search using the MyISAM engine and use that for the full-text search? This way I'm just searching a copy of the data and if anything happens to that data it's not as big of a deal because it can always be re-created. Or is this an awkward way of doing this that should be avoided? Thanks.

  Read the article

• How to easily get the unmatched condition in mysql

  - by leivli

  I have a "server" table which has a column named 'SN' in mysql, when do query to retrive servers with some sns from 'sn1' to 'sn10000', we can: select * from server where sn in ('sn1','sn2','sn3',...'sn10000'); If there is only one sn in 'sn1'-'sn10000' which not exists in database, then the query above will retrive 9999 rows of result. The question is how can I easily get which one in 'sn1'-'sn10000' is not exists in database except the additional work, such as handling the result with shell script etc. I have an ugly sql like below can use: select * from (select 'sn1' as sn union select 'sn2' union select 'sn3' .... union select 'sn10000') as SN where not exists (select id from server where server.sn=SN.sn); Is Anyone has other better methods? Thanks.

  Read the article

• Getting mysql syntax error and cant find source

  - by eGGzy

  I have function that updates log table. function wslog($userID, $log, $where) { safe_query("INSERT INTO ".PREFIX."log ( time, userID, log, where ) values( '".time()."', '".$userID."', '".$log."', '".$where."' ) "); } And I have this php code: wslog($userID, 'server|'.mysql_insert_id().'', 'servers'); But I keep getting syntax error: Query failed: errorno=1064 error=You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where ) values( '1269208030', '1', 'server|14', 'servers' )' at line 1 query=INSERT INTO ws_DII_log ( time, userID, log, where ) values( '1269208030', '1', 'server|14', 'servers' )

  Read the article

• converting ip to long string in mysql command !

  - by Mac Taylor

  hey guys I'm trying to use ip2country technique to show my users'flags simple thing to do is to write a sql statement and show users from session table and then query to see if their ip is in what range and show their flags it simple but dengerous becuase when showing 300 users online and fetching from session table and then query once again ip2country table to get the flags there will be surelly memory overusage problem now i wrote a query to make it in one query : SELECT s.session_ip,ipc.* FROM session s left join ip2country ipc ON ipc.ip_lo<=s.session_ip AND ipc.ip_hi>=s.session_ip WHERE s.session_time > '".( time() - 60) )."' now its clear above query is wrong cause ips saved in ip2country table is long string 1000013824 and ips saved in session table are real ip e.g. 193.169.0.0 now i know how to convert from ip to long in php ip2long() but is there any command in mysql to do it with out building another query !?

  Read the article

• select subquery cause mysql server to be unresponsive

  - by Xiao

  DELETE FROM item_measurement WHERE measurement_id IN ( SELECT (-id) AS measurement_id FROM invoice_item WHERE invoice_id = 'A3722' ) I've really tried hard to find what's wrong with the code. I tried to run this in a php page, which doesn't respond. I also tried the same line in phpmyadmin, infinite spinning circle and I had to restart the server(MAMP on Mac 10.9). No error was given in browser/ If I run the delete and select separately, they both finish very quickly. I don't think it's a performance issue because the separate run took < 0.1 sec each. Thanks for any help in advance. Edit: I also found that a simple select statement will also suspend mysql: select * from item_measurement where measurement_id in (select -id from invoice_item where invoice_id='A3722')

  Read the article

• PHP MySQL Insert Help

  - by user364333

  Hey I am trying to make a page that inserts some strings into a MySQL table but it just dosn't seem to be working for me. Here is the code I am using at the moment. <?php mysql_connect($address, $username, $password); @mysql_select_db($database) or die("Unable to select database"); $query = "insert INTO user (movieid, moviename)('" . $id . "','" . $name . "') or die(mysql_error())"; mysql_query($query); mysql_close(); ?> Where am i going wrong?

  Read the article

• Need to get all referrals ID with using MYSQL

  - by Fero

  Hi all, I having a referral table like below. > id referredByID referrerID > > 1001 1 2 > > 1002 2 3 > > 1003 2 4 > > 1004 5 7 From the above table structure i need to get the users whom i referred and the users whom are referred by their referrals. For Example: I am referredByID-1 I referred the ID - 2 Now the ID - 2 referred ID -3 And in the same case ID-2 referred ID - 4 Now my output needs to be look like: Referrals Done By Me: id - 2 id - 3 id - 4 How can this be done using MYSQL. Any help will be appreciated.. Thanks in advance...

  Read the article

• C# mysql one return last_insert_id

  - by Bernhard

  I am trying to create a method in which I can exequte mysql UPDATE, DELETE or INSERT query. The method must work when with an INSERT I ask or do not ask the last_insert_id(). Below is the code that I have at the moment: public int executeUID(MySqlCommand msCommand) { try { this.Open(); msCommand.Connection = this.msCon; return int.Parse(msCommand.ExecuteScalar().ToString()); } catch (MySqlException ex) { throw ex; } finally { this.Close(); } } The problem with this is is that when I use an insert query that returns a last_insert_id() the method works greatly. But when the query doesn't return an last_insert_id() the method malfunctions. How can I get this method to work?

  Read the article

• How to store matrix information in MySQL?

  - by dedalo

  Hi, I'm working on an application that analizes music similarity. In order to do that I proccess audio data and store the results in txt files. For each audio file I create 2 files, 1 containing and 16 values (each value can be like this:2.7000023942731723) and the other file contains 16 rows, each row containing 16 values like the one previously shown. I'd like to store the contents of these 2 file in a table of my MySQL database. My table looks like: Name varchar(100) Author varchar (100) in order to add the content of those 2 file I think I need to use the BLOB data type: file1 blob file2 blob My question is how should I store this info in the data base? I'm working with Java where I have a double array containing the 16 values (for the file1) and a matrix containing the file2 info. Should I process the values as strings and add them to the columns in my database? Thanks

  Read the article

• Need help searching a MySQL db using a SELECT DISTINCT result

  - by user1695645

  So I am new to MySQL and am having a bit of trouble. I have one table called book_genres and another called books. book_genres +-------+---------+ |book_id| genre | +-------+---------+ | 1 | Horror | | 1 | Comedy | | 2 | Romance | | 2 | Comedy | +-------+---------+ books +-------+---------+ |book_id| title | +-------+---------+ | 1 | A Book | | 2 | B Book | | 3 | C Book | +-------+---------+ I am using the following command to pull all the book_ids that have 3 selected genres: SELECT DISTINCT a.book_id, b.genre AS genre1, c.genre AS genre2, d.genre AS genre3 FROM book_genres a JOIN book_genres b ON a.book_id = b.book_id AND b.genre LIKE 'Romance' JOIN book_genres c ON a.book_id = c.book_id AND c.genre LIKE 'Action' JOIN book_genres d ON a.book_id = d.book_id AND d.genre LIKE 'Comedy' GROUP BY book_id What I want to do is now pull all of the book titles from the books table using the book_ids found in this search. I'm not sure if there is an easier way to do this or not, but this was all that I could come up with. Thank you for anyone who can help!

  Read the article

• Create a PHP cache system in MySQL database?

  - by Zach Smith

  I'm creating a web service that often scrapes data from remote web pages. After scraping this data, I have a simple multidimensional array of information to use. The scraping process is fairly taxing on my server, and the page load takes a while. I was considering adding a simple cache system using a MySQL database, where I create one row per remote web page with a the array of information pulled from it stored as a JSON encoded string. Is this a good enough system? Or would something like a text file per web page be a better idea?

  Read the article

• Select unseen random row from MySQL table

  - by user1476925

  We have a list of questions in a MySQL database and want it to show a random approved question to the user. When you click the Random button, we want another random question to be shown, but not any of the ones the user has already seen. Right now the script looks like this: <?php mysql_connect("localhost", "username", "password") or die(mysql_error()); mysql_select_db("aldrig") or die(mysql_error()); $result = mysql_query("SELECT * FROM spg WHERE approved='1' ORDER BY RAND() LIMIT 1;") or die(mysql_error()); while($row = mysql_fetch_array( $result )) { echo "<div class='contentTitle'><h1>"; echo $row['text']; echo "</h1></div>"; } ?>

  Read the article

• Separating MySQL SELECT statement based on data in a column

  - by NightMICU

  Hi everyone, I need to retrieve data (vehicle details, in this case) from a MySQL table tabled and then loop through the results to generate separate lists for each category of vehicle. Is there a simple way to do this without having to have a SELECT statement for each type of vehicle? If I were just doing this for one category, I would use the following: <?php $sql = "SELECT * FROM apparatus WHERE vehicleType = 'Support'; $getSQL = mysql_query($sql); ?> <ul> <?php while ($vehicleData = mysql_fetch_assoc($getSQL)) {?> <li><?php echo $vehicleData['name'];?></li> <?php } ?> </ul> ..etc. Need to do this for four different types of vehicles. Thanks!

  Read the article

• Error comparing hash to hashed mysql password (output values are equal)

  - by Charlie

  Im trying to compare a hashed password value in a mysql database with the hashed value of an inputted password from a login form. However, when I compare the two values it says they aren't equal. I removed the salt to simply, and then tested what the outputs were and got the same values $password1 = $_POST['password']; $hash = hash('sha256', $password1); ...connect to database, etc... $query = "SELECT * FROM users WHERE username = '$username1'"; $result = mysql_query($query); $userData = mysql_fetch_array($result); if($hash != $userData['password']) //incorrect password { echo $hash."|".$userData['password']; die(); } ...other code... Sample output: 7816ee6a140526f02289471d87a7c4f9602d55c38303a0ba62dcd747a1f50361| 7816ee6a140526f02289471d87a7c4f9602d55c38303a0ba62dcd747a1f50361 Any thoughts?

  Read the article

• MySQL Query Ranking

  - by eft0

  Hey there, I have this MySQL query for list a "Vote Ranking" for "Actions" and it work fine, but I want what the "id_user" doesn't repeat, like made a "DISTINCT" in this field. SELECT count(v.id) votos, v.id_action, a.id_user, a.id_user_to, a.action, a.descripcion FROM votes v, actions a WHERE v.id_action = a.id GROUP BY v.id_action ORDER BY votos DESC The result: votes act id_user 3 3 745059251 2 20 1245069513 2 23 1245069513 2 26 100000882722297 2 29 1245069513 2 44 1040560484 2 49 1257441644 2 50 1040560484 The expected result votes act id_user 3 3 745059251 2 20 1245069513 2 26 100000882722297 2 44 1040560484 2 49 1257441644 2 50 1040560484 Thanks in advanced!

  Read the article

• mysql NO_AUTO_VALUE_ON_ZERO problem

  - by every_answer_gets_a_point

  at least i think that is what the problem is. i am updating a mysql database through excel using an odbc like this: With rs .AddNew ' create a new record ' add values to each field in the record .Fields("datapath") = dpath .Fields("analysistime") = atime .Fields("reporttime") = rtime .Fields("lastcalib") = lcalib .Fields("analystname") = aname .Fields("reportname") = rname .Fields("batchstate") = "bstate" .Fields("instrument") = "NA" .Update ' stores the new record End With besides the fields above, i also have a primary key auto_increment field called rowid for some reason the only field that gets updated is instrument. none of the other fields get updated.. however, when i try to do the same updating on a table that has no ROWID auto_increment field, it works perfectly has anyone else experienced this kind of issue before?

  Read the article

• Ensuring uniqueness on a varchar greater than 255 in MYSQL/InnoDB

  - by Vijay Boyapati

  I have a table which contains HTML entries for news pages. When I initially designed it I used URL as the primary key. I've learned the error of my ways because left-joining is super slow. So I want to redesign the table with an integer (id) primary key, but still keep the rows unique based on the URL. The problem is that I've found URLs longer than 255 characters, and MySQL isn't letting my create a key on the URL. I'm using an InnoDB/UTF8 table. From what I understand it's using multiple bytes per character with a limit of 766 bytes for the key (in InnoDB). I would really love suggestions on an elegant way of keeping the rows unique based on URL, while using an integer primary key. Thanks!

  Read the article

• mysql row counter

  - by David19801

  Hello, I have a mysql table. It has auto increment on the id. but I regularly delete rows so the numbers are all over the place. I need to get the last n rows out, but because of deletions, the common way of using the max of the autoincremented id column doesn't work well... 1 - Is their another way to get the bottom 50? 2 - Is their a way to get rows by actual row number? so if I have 4 rows labelled 1,2,3,4 delete row 2 then it will become 1,2,3 rather than 1,3,4?

  Read the article

• wanted to get all dates in mysql result

  - by PankajK

  I have mysql table called user(id, name, join_on) join on is a date field what I want is to show in each day how many uses has been created I can use group by but it will only give me the dates when users get added like if date 4/12/10 5 users added 4/13/10 2 users added 4/15/10 7 users added here date 4/14/10 is missing and I want listing of all dates in one month. I have one solution for it by creating another table only for adding date and that table will left join my users table on join_on and will give total result but I don't want to do that as for creating that I need to create and add entries in date table please suggest the different approach for doing so. Thank you.

  Read the article

• Getting Null value with JSON from MySQL, how to retrive data from MySQL to JSON correctly?

  - by sky

  I'm using following code but cannot return data from MySQL. This is the output: <script type="text/javascript"> var somethings= [null,null,null]; </script> It does have three post, but I couldn't get the title(message) output. EDIT: this is the code I'm using: <?php $session = mysql_connect('localhost','name','pass'); mysql_select_db('dbname', $session); $result= mysql_query('SELECT * FROM posts', $session); $somethings= array(); while ($row= mysql_fetch_assoc($result)) { $somethings[]= $row['something']; } ?> <script type="text/javascript"> var somethings= <?php echo json_encode($somethings); ?>; </script> This is the table: message Try iPhone post! Welcome to Yo~ :) ??!

  Read the article

< Previous Page | 135 136 137 138 139 140 141 142 143 144 145 146  | Next Page >