Search Results

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

Page 168/595 | < Previous Page | 164 165 166 167 168 169 170 171 172 173 174 175  | Next Page >

• Mysql - What's wrong with the query...?

  - by SpikETidE

  Hi everybody.... I am trying to query a database to find the following If a customer searches for a hotel in a city between dates A and B, find and return the hotels in which rooms are free between the two dates. There will be more than one room in each room type(i.e. 5 Rooms in type A, 10 rooms in Type B etc) and we have to query the db to find only those hotels in which there is atleast one room free in atleast one type. This is my table structure.... **Structure for table 'reservations'** reservation_id hotel_id room_id customer_id payment_id no_of_rooms check_in_date check_out_date reservation_date **Structure for table 'hotels'** hotel_id hotel_name hotel_description hotel_address hotel_location hotel_country hotel_city hotel_type hotel_stars hotel_image hotel_deleted **Structure for table 'rooms'** room_id hotel_id room_name max_persons total_rooms room_price room_image agent_commision room_facilities service_tax vat city_tax room_description room_deleted And this is my query $city_search = '15'; $check_in_date = '29-03-2010'; $check_out_date = '31-03-2010'; $dateFormat_check_in = "DATE_FORMAT('$reservations.check_in_date','%d-%m-%Y')"; $dateFormat_check_out = "DATE_FORMAT('$reservations.check_out_date','%d-%m-%Y')"; $dateCheck = "$dateFormat_check_in >= '$check_in_date' AND $dateFormat_check_out <= '$check_out_date'"; $query = "SELECT $rooms.room_id, $rooms.room_name, $rooms.max_persons, $rooms.room_price, $hotels.hotel_id, $hotels.hotel_name, $hotels.hotel_stars, $hotels.hotel_type FROM $hotels,$rooms,$reservations WHERE $hotels.hotel_city = '$city_search' AND $hotels.hotel_id = $rooms.hotel_id AND $hotels.hotel_deleted = '0' AND $rooms.room_deleted = '0' AND $rooms.total_rooms - (SELECT SUM($reservations.no_of_rooms) as tot FROM $reservations WHERE $dateCheck GROUP BY $reservations.room_id) > '0'"; The number of rooms already reserved in each room type in each hotel will be stored in the reservations table... The thing is the query doesn't return any result at all...even though it should if i calculate it myself manually... I tried running the sub-query alone and i don't get any result... And i have lost quite some amount of hair trying to de-bug this query from yesterday... What's wrong with this...? Or is there a better way to do what i mentioned above...? Thanks for your time... Edit : Code edited to remove an bud... thanks to

  Read the article

• Mysql InnoDB performance optimization and indexing

  - by Davide C

  Hello everybody, I have 2 databases and I need to link information between two big tables (more than 3M entries each, continuously growing). The 1st database has a table 'pages' that stores various information about web pages, and includes the URL of each one. The column 'URL' is a varchar(512) and has no index. The 2nd database has a table 'urlHops' defined as: CREATE TABLE urlHops ( dest varchar(512) NOT NULL, src varchar(512) DEFAULT NULL, timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY dest_key (dest), KEY src_key (src) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Now, I need basically to issue (efficiently) queries like this: select p.id,p.URL from db1.pages p, db2.urlHops u where u.src=p.URL and u.dest=? At first, I thought to add an index on pages(URL). But it's a very long column, and I already issue a lot of INSERTs and UPDATEs on the same table (way more than the number of SELECTs I would do using this index). Other possible solutions I thought are: -adding a column to pages, storing the md5 hash of the URL and indexing it; this way I could do queries using the md5 of the URL, with the advantage of an index on a smaller column. -adding another table that contains only page id and page URL, indexing both columns. But this is maybe a waste of space, having only the advantage of not slowing down the inserts and updates I execute on 'pages'. I don't want to slow down the inserts and updates, but at the same time I would be able to do the queries on the URL efficiently. Any advice? My primary concern is performance; if needed, wasting some disk space is not a problem. Thank you, regards Davide

  Read the article

• mysql join Two tables to get records

  - by Saranya

  Hai guys, I have two tables Incharge and property. My property table has three fields 1stIncharge,2ndIncharge and 3rdIncharge. InchargeId is set as foreign key for all the above fields in the property table.. How to write a select statement that joins both the table.. I ve tried a bit but no result select P.Id,P.Name,P.1stIncharge,P.2ndIncharge,P.3rdIncharge,I.Id from Property as P join Incharge as I where (\\How to give condition here \\) Guys 3 fields P.1stIncharge, P.2ndIncharge, P.3rdIncharge has foreign key I.Id Edit: select P.Id,P.Name,P.1stIncharge,P.2ndIncharge,P.3rdIncharge,I1.Id from Property as P inner join Incharge as I1 on I1.Id=P.1stIncharge inner join Incharge as I2 on I2.Id=P.2ndIncharge inner join Incharge as I3 on I3.Id=P.3rdIncharge and this query working

  Read the article

• Why can i read dirty rows in MySql

  - by acidzombie24

  I cant believe this, i always throught the below would be concurrency safe. I write to a row in one transaction and i am able to read the dirty value from another transaction/command/connection! Why is this possible (not my main question) isnt this not desired and cause more troubles!?! Anyways, i expected that once i write to a row nothing else will be able to read to the row until the transaction is finished. And at least if the row can be still read that the clean (original) value will be read. (but maybe that would cause problems as well if the transaction doesnt use the newly commited data from the other transaction when it is ran) I would like count to == 11. I thought this would be safe in all variants of sql. What can i do to either 1) Not read the dirty value but clean 2) Have that row be locked until the transaction is finished? static MySqlConnection MakeConn() { string connStr = "server=192.168.126.128;user=root;database=TestDB;port=3306;password=a;"; MySqlConnection conn = new MySqlConnection(connStr); conn.Open(); return conn; } static Semaphore sem1 = new Semaphore(1, 1); static Semaphore sem2 = new Semaphore(1, 1); static void Main2() { Console.WriteLine("Starting Test"); // sem1.WaitOne(); Console.WriteLine("1W"); sem2.WaitOne(); Console.WriteLine("2W"); Thread oThread = new Thread(new ThreadStart(fn2)); oThread.Start(); var conn = MakeConn(); var cmd = new MySqlCommand(@" CREATE TABLE IF NOT EXISTS Persons ( P_Id int NOT NULL, name varchar(255), count int, PRIMARY KEY (P_Id) )", conn); cmd.ExecuteNonQuery(); cmd.CommandText = "delete from Persons; insert into Persons(name, count) VALUES('E', '4');"; cmd.ExecuteNonQuery(); cmd.CommandText = "select count from Persons;"; var count = (int)cmd.ExecuteScalar(); Console.WriteLine("Finish inserting. v={0}", count); sem2.Release(); Console.WriteLine("2R"); sem1.WaitOne(); Console.WriteLine("1W"); Console.WriteLine("Starting transaction"); using (var tns = conn.BeginTransaction()) { cmd.CommandText = "update Persons set count=count+1"; cmd.ExecuteNonQuery(); cmd.CommandText = "select count from Persons;"; count = (int)cmd.ExecuteScalar(); Console.WriteLine("count is {0}", count); sem2.Release(); Console.WriteLine("2R"); sem1.WaitOne(); Console.WriteLine("1W"); count += 5; //10 cmd.CommandText = "update Persons set count=" + count.ToString(); cmd.ExecuteNonQuery(); cmd.CommandText = "select count from Persons;"; count = (int)cmd.ExecuteScalar(); Console.WriteLine("count is {0}", count); tns.Commit(); } Console.WriteLine("finished transaction 1"); sem2.Release(); Console.WriteLine("2R"); sem1.WaitOne(); Console.WriteLine("1W"); cmd.CommandText = "select count from Persons;"; count = (int)cmd.ExecuteScalar(); Console.WriteLine("count is {0}", count); sem2.Release(); Console.WriteLine("2R"); //sem1.WaitOne(); Console.WriteLine("1W"); } static void fn2() { int count; Console.WriteLine("Starting thread 2"); sem2.WaitOne(); Console.WriteLine("1W"); var conn = MakeConn(); var cmd = new MySqlCommand("", conn); sem1.Release(); Console.WriteLine("1R"); sem2.WaitOne(); Console.WriteLine("2W"); using (var tns = conn.BeginTransaction()) { cmd.CommandText = "update Persons set count=count+1"; cmd.ExecuteNonQuery(); cmd.CommandText = "select count from Persons;"; count = (int)cmd.ExecuteScalar(); Console.WriteLine("count is {0}", count); sem1.Release(); Console.WriteLine("1R"); sem2.WaitOne(); Console.WriteLine("2W"); tns.Commit(); } Console.WriteLine("finished transaction 2"); sem1.Release(); Console.WriteLine("1R"); sem2.WaitOne(); Console.WriteLine("2W"); cmd.CommandText = "select count from Persons;"; count = (int)cmd.ExecuteScalar(); Console.WriteLine("count is {0}", count); //should be 11. 4 + 1x2(one each thread) += 5 from first thread == 11 sem1.Release(); Console.WriteLine("1R"); } console Starting Test 1W 2W Starting thread 2 Finish inserting. v=4 2R 1W 1R 1W Starting transaction count is 5 2R 2W count is 6 1R 1W count is 10 finished transaction 1 2R 2W finished transaction 2 1R 1W count is 10 2R 2W count is 10 1R

  Read the article

• Return order of MySQL SHOW COLUMNS

  - by rich

  Hey guys. Simple one this, but one I can't seem to find any information on so here goes. I need to find the columns in a specific table, which is no problem.... SHOW COLUMNS FROM tablename LIKE '%ColumnPrefix%'; But I need to know what order they will be returned, preferable by choosing to order the results ascending alphabetically. I have had no luck with using ORDER BY Field. Any ideas? Cheers!

  Read the article

• getting a date array from a mysql database?

  - by user296516

  Hi guys, I have a database with date field is this format "2010.06.11. | 10:26 13" What is need is a php array that would hold all the different dates, .i.e. array[0] = "2010.06.09." array[1] = "2010.06.10." array[2] = "2010.06.11." Currently I am doing it by selecting the whole table, then looping through the result and adding the date substr to an array if it is not already there. But maybe there is a faster way? Thanks.

  Read the article

• php, mySQL - how to create a table?

  - by user296516

  Hi guys, I have written a code that should check weather there is a table called imei.$addimei and, if not, create it... $userdatabase = mysqli_connect('localhost', 'root', 'marina', 'imei'); ... $result = mysqli_query($userdatabase, "SELECT * FROM imei".$addimei."" ); if ( !$result ) { echo('creating table...'); /// if no such table, make one! mysql_query ( $userdatabase, 'CREATE TABLE imei'.$addimei.'( ID int NOT NULL AUTO_INCREMENT, PRIMARY KEY(ID), EVENT varchar(15), TIME varchar(25), FLD1 varchar(35), FLD2 varchar(35), IP varchar(25), )' ); } Yet the CREATE TABLE somehow doesn't seem to work. Warning: mysql_query() expects parameter 1 to be string, object given in C:\xampp\xampp\htdocs\mobi\mainmenu.php on line 564 Any idea's what wrong? Thanks!

  Read the article

• do's and don'ts for writing mysql queries

  - by nik

  One thing I always wonder while writing query is that am I writing most optimized query or not? I know certain things like: 1) using SELECT field1, filed2 instead of SELECT * 2) Giving proper indexes to the tables but I am sure there are more things that should be kept in mind for writing queries, since most of the database can only grow more and optimal query will help gr8 in execution time, Can u share some tips and tricks on writing queries?

  Read the article

• MySQL: updating a row and deleting the original in case it becomes a duplicate

  - by Silvio Donnini

  I have a simple table made up of two columns: col_A and col_B. The primary key is defined over both. I need to update some rows and assign to col_A values that may generate duplicates, for example: UPDATE `table` SET `col_A` = 66 WHERE `col_B` = 70 This statement sometimes yields a duplicate key error. I don't want to simply ignore the error with UPDATE IGNORE, because then the rows that generate the error would remain unchanged. Instead, I want them to be deleted when they would conflict with another row after they have been updated I'd like to write something like: UPDATE `table` SET `col_A` = 66 WHERE `col_B` = 70 ON DUPLICATE KEY REPLACE which unfortunately isn't legal in SQL, so I need help finding another way around. Also, I'm using PHP and could consider a hybrid solution (i.e. part query part php code), but keep in mind that I have to perform this updating operation many millions of times. thanks for your attention, Silvio Reminder: UPDATE's syntax has problems with joins with the same table that is being updated

  Read the article

• Php fetch rows from multiple MySQL tables

  - by Jon McIntosh

  Right now I am fetching all of the rows from one of my tables: query = "SELECT * FROM thread WHERE threadid = 2 ORDER BY threadid DESC"; $result = mysql_query($query); $num_rows = mysql_num_rows($result); if((!is_bool($result) || $result) && $num_rows) { while($row = mysql_fetch_array($result)) { $thread = $row['title']; $threadID = $row['threadid']; $poster = $row['postusername']; } What I want to do is go to another table on my database: "post_display", and get the row 'text' where the threadid = 2.

  Read the article

• Help with MySQL Query using CASE statement

  - by hairdresser-101

  I am trying to group a number of customers together based on their "Head Office" or "Parent" location. THis works ok except for a flaw which I didn't forsee when I was developing my system... For customers that did not have a "Parent" (standalone business) I defaulted the parent_id to 0. Therefore, my data would look like this: id parent_id customer 1 0 CustName#1 2 4 CustName#2 - Melbourne 3 4 CustName#2 - Sydney 4 0 CustName#2 (Head Office) What I want to do is Group my results together so that I have one row for CustName#1 and one row for CustName#2 BUT my problem is that there is no parent record for parent_id=0 and these rows are being excluded when using an inner join. I've tried using a case statement but that is not working either (parents are still being ignored) Any help would be greatly appreciated. Here is my query (My CASE is basically trying to get the business_name from the customer table based on the parent_id EXCEPT when the parent_id = 0, THEN just use the customer_name that is listed in the job_summary table): SELECT js.month_of_year, (CASE js.parent_id WHEN 0 THEN js.customer_name ELSE c.business_name END) as customer, SUM(js.jobs), SUM(js.total_cost), sum(js.total_sell) FROM JOB_SUMMARY js INNER JOIN customer c on js.parent_id=c.id group by js.month_of_year, (CASE c.parent_id WHEN 0 THEN js.customer_name ELSE c.business_name END) ORDER BY `customer` ASC

  Read the article

• double left MYSQL join?

  - by Haroldo

  I've been trying left joins but as there are 2 joins, i think the problem is the 2nd join roots from table_B not table_A. i am not getting any results where there is the required data in the db. I am not getting a query error the query (simplified) SELECT events.*, ven.*, events_genres.* FROM events LEFT JOIN ven //OPTIONAL JOIN ON events.ven_id = ven.ven_id //OPTIONAL JOIN LEFT JOIN events_genres //REQUIRED JOIN ON events.event_id = events_genres.event_id //REQUIRED JOIN WHERE events.date >= '$this->now' AND WHERE events_genres.g_id = $g_id //REQUIRED MATCH ORDER BY date ven = optional, i'll have the info if its there. events_genres = required, i dont want any results that do not have a genre

  Read the article

• Mysql partitioning with foreign keys

  - by Industrial

  Hi! What would be an appropriate way to get around this issue? To leave either partitioning or the foreign keys out would not seem like a good idea to me. I'll guess that there is a workaround for this? Thanks!

  Read the article

• MySQL VARCHAR like Data type with MIN and MAX character lengths

  - by Phill Pafford

  Looking for a data type that is similar to VARCHAR as to the values I would like, but is there a way to have a MIN/MAX character length? VARCHAR(6,10) This would have a minimum of 6 characters and a maximum of 10 characters.

  Read the article

• mysql concat all field table

  - by hafizan

  Is there a way we can concat all field in the table(1 sql statement)(automatic) ? The reason was before user updated or delete a record,the record will push to another table for future reference.

  Read the article

• MySQL - display rows of names and addresses grouped by name, where name occures more than once

  - by Stoob

  I have two tables, "name" and "address". I would like to list the last_name and joined address.street_address of all last_name in table "name" that occur more than once in table "name". The two tables are joined on the column "name_id". The desired output would appear like so: 213 | smith | 123 bluebird | 14 | smith | 456 first ave | 718 | smith | 12 san antonia st. | 244 | jones | 78 third ave # 45 | 98 | jones | 18177 toronto place | Note that if the last_name "abernathy" appears only once in table "name", then "abernathy" should not be included in the result. This is what I came up with so far: SELECT name.name_id, name.last_name, address.street_address, count(*) FROM `name` JOIN `address` ON name.name_id = address.name_id GROUP BY `last_name` HAVING count(*) > 1 However, this produces only one row per last name. I'd like all the last names listed. I know I am missing something simple. Any help is appreciated, thanks!

  Read the article

• Multiple many-to-many JOINs in a single mysql query without Cartesian Product

  - by VWD

  At the moment I can get the results I need with two seperate SELECT statements SELECT COUNT(rl.refBiblioID) FROM biblioList bl LEFT JOIN refList rl ON bl.biblioID = rl.biblioID GROUP BY bl.biblioID SELECT GROUP_CONCAT( CONCAT_WS( ':', al.lastName, al.firstName ) ORDER BY al.authorID ) FROM biblioList bl LEFT JOIN biblio_author ba ON ba.biblioID = bl.biblioID JOIN authorList al ON al.authorID = ba.authorID GROUP BY bl.biblioID Combining them like this however SELECT GROUP_CONCAT( CONCAT_WS( ':', al.lastName, al.firstName ) ORDER BY al.authorID ), COUNT(rl.refBiblioID) FROM biblioList bl LEFT JOIN biblio_author ba ON ba.biblioID = bl.biblioID JOIN authorList al ON al.authorID = ba.authorID LEFT JOIN refList rl ON bl.biblioID = rl.biblioID GROUP BY bl.biblioID causes the author result column to have duplicate names. How can I get the desired results from one SELECT statement without using DISTINCT? With subqueries?

  Read the article

• mysql order-by original "where order"

  - by Benjamin Dobnikar

  I have this order-by problem I canot crack. I select from my table like this: SELECT * FROM 'sidemodules' WHERE name = 'module1' OR name = 'module2' OR 'name3' Which returns me the modules I want. But the modules lie the table, say in this order: module3 module1 module2 And they are returned to me in this order. How can I get them to display in order AS IN THE WHERE CLAUSE (1,2,3) ? Big thanks!

  Read the article

• Problem importing mysql triggers generated from mysqldump

  - by OM The Eternity

  I am using phpmyadmin for using the mysqldump query, but as per my requirement i have to create a new database which is clone of the previous one, now in this case when i import the main DB it contain all the trigger information as well with the DB name mentioned in it.. As i import this DB to new one my triggers get imported as well but the trigger_schema are not changed as per new DB.. What could be done to get resolve this problem?

  Read the article

• Storing connections between people in a MySql database

  - by daniel

  I would like to create a database where I store the links between people. I then need to bring back the records detailing which people are connected to which others. I need to output: person1 is connected to person2 is connected with person3 etc. How can I get started with this task?

  Read the article

• Mysql Database Question about Large Columns

  - by murat

  Hi, I have a table that has 100.000 rows, and soon it will be doubled. The size of the database is currently 5 gb and most of them goes to one particular column, which is a text column for PDF files. We expect to have 20-30 GB or maybe 50 gb database after couple of month and this system will be used frequently. I have couple of questions regarding with this setup 1-) We are using innodb on every table, including users table etc. Is it better to use myisam on this table, where we store text version of the PDF files? (from memory usage /performance perspective) 2-) We use Sphinx for searching, however the data must be retrieved for highlighting. Highlighting is done via sphinx API but still we need to retrieve 10 rows in order to send it to Sphinx again. This 10 rows may allocate 50 mb memory, which is quite large. So I am planning to split these PDF files into chunks of 5 pages in the database, so these 100.000 rows will be around 3-4 million rows and couple of month later, instead of having 300.000-350.000 rows, we'll have 10 million rows to store text version of these PDF files. However, we will retrieve less pages, so again instead of retrieving 400 pages to send Sphinx for highlighting, we can retrieve 5 pages and it will have a big impact on the performance. Currently, when we search a term and retrieve PDF files that have more than 100 pages, the execution time is 0.3-0.35 seconds, however if we retrieve PDF files that have less than 5 pages, the execution time reduces to 0.06 seconds, and it also uses less memory. Do you think, this is a good trade-off? We will have million of rows instead of having 100k-200k rows but it will save memory and improve the performance. Is it a good approach to solve this problem and do you have any ideas how to overcome this problem? The text version of the data is used only for indexing and highlighting. So, we are very flexible. Thanks,

  Read the article

• mySQL not saving data?

  - by tony noriega

  i have a PHP contact form that submits data, and an email...: <?php $dbh=mysql_connect ("localhost", "username", "password") or die ('I cannot connect to the database because: ' . mysql_error()); mysql_select_db ("guest"); if (isset($_POST['submit'])) { if (!$_POST['name'] | !$_POST['email']) { echo"<div class='error'>Error<br />Please provide your Name and Email Address so we may properly contact you.</div>"; } else { $age = $_POST['age']; $name = $_POST['name']; $gender = $_POST['gender']; $email = $_POST['email']; $phone = $_POST['phone']; $comments = $_POST['comments']; $query = "INSERT INTO contact_us (age,name,gender,email,phone,comments) VALUES ('$age','$name','$gender','$email','$phone','$comments')"; mysql_query($query); mysql_close(); $yoursite = "Mysite "; $youremail = $email; $subject = "Website Guest Contact Us Form"; $message = "$name would like you to contact them Contact PH: $phone Email: $email Age: $age Gender: $gender Comments: $comments"; $email2 = "[email protected]"; mail($email2, $subject, $message, "From: $email"); echo"<div class='thankyou'>Thank you for contacting us,<br /> we will respond as soon as we can.</div>"; } } ?> The email is coming through fine, but the data is not storing the dbase... am i missing something? Its the same script as i use on another contact us page, only difference is instead of parsing the data on teh same page, i now send this data to a "thankyou.php" page... i tried changing $_POST to $_GET but that killed the page... what am i doing wrong?

  Read the article

• MySql left join on several regs

  - by egidiocs

  Hi there! I have this table1 idproduct(PK) | date_to_go 1 2010-01-18 2 2010-02-01 3 2010-02-21 4 2010-02-03 and this other table2 that controls date_to_go updates id | idproduct(FK) | prev_date_to_go | date_to_go | update_date 1 1 2010-01-01 2010-01-05 2009-12-01 2 1 2010-01-05 2010-01-10 2009-12-20 3 1 2010-01-10 2010-01-18 2009-12-20 4 3 2010-01-20 2010-02-03 2010-01-05 So, in this example, for table1.idproduct #1 2010-01-18 is the actual date_to_go and 2010-01-01 (table2.prev_date_to_go, first reg) is the original date_to_go . using this query select v.idproduct, v.date_to_go, p.prev_date_to_go original_date_to_go from table1 v left join produto_datas p on p.idproduto = v.idproduto group by (v.idproduto) order by v.idproduto can I assume that original_date_to_go will be the first related reg of table2? idproduct | date_to_go | original_date_to_go 1 2010-01-18 2010-01-01 2 2010-02-01 NULL 3 2010-02-21 2010-01-20 4 2010-02-03 NULL

  Read the article

• Use of HAVING in MySQL

  - by KBrian

  I have a table from which I need to select all persons that have a first name that is not unique and that that set should be selected only if among the persons with a similar first name, all have a different last name. Example: FirstN LastN Bill Clinton Bill Cosby Bill Maher Elvis Presley Elvis Presley Largo Winch I want to obtain FirstN LastN Bill Clinton or FirstN LastN Bill Clinton Bill Cosby Bill Maher I tried this but it does not return what I want. SELECT * FROM Ids GROUP BY FirstN, LastN HAVING (COUNT(FirstN)>1 AND COUNT(LastN)=1)) [Edited my post after Aleandre P. Lavasseur remark]

  Read the article

• What is an index in MySQL?

  - by Eric

  http://i.imgur.com/JdsUK.jpg I created a table like the picture above. What are the "Indexes"? primary key? unique? It works well without setting indexes.. What do they do? why do I need them? Also, I set all String fields to TEXT because I didn't know how many characters I need. Is this a good idea? I don't see any difference. Thanks!

  Read the article

< Previous Page | 164 165 166 167 168 169 170 171 172 173 174 175  | Next Page >