Hi! I'm looking for a way to optimize one SQL query that I have. I'm trying to get how many poems with a certain genre. Query looks like this: SELECT COUNT(*) FROM `poems` WHERE `id` IN ( SELECT `poem_id` FROM `poems_genres` WHERE `genre_title` = 'derision' ...
Hello, I'm building a search page and I'm a bit unsure on what I should in regard to sanitizing and validating user input. The search page has filters which are passed via querystrings. (some passed from inputs and others from links with checkbox/radio like behavior) What should I look out for in this situation? Can I be safe using ju...
Objective: The Leaves Management and Payroll system requires the official weekend hodiday scheme stored in the database to be referred to while generating the monthly salary for each employee in the organisation. Generating flexible Database Schema for storing the weekend scheme is the objective. Problem: The database schema should be f...
I am having trouble filtering data out a relational table. The query is part of a join, but I am stuck on a basic part. I need to remove all the results with a certain id if the condition is found. query similar to: select * from colors where color != 'red' group by id id color 1 red 1 blue 1 blue 2 green 2 blue 3 gre...
Hi I have encountered a problem while inserting a large amounts of text into my MySql database. Can I please get any pointers (what column types to use & any other issues I need to know) as to how I can achieve this, the code works fine with small amounts. Thanks in advance. Clarification The text blocks have around 7,000 characters....
I'm having a little problem displaying data from a database... I have two database tables: categories(cat_id, cat_name) menu_items(menu_id, menu_name, cat_id, menu_description, menu_price) I would like to display data as: Categorie 1 Name cat_id = 1, menu_name, menu_description, menu_price cat_id = 1, menu_name, menu_descripti...
I have these tables: customer: customer_id vat_number =========== ========== 1 ES-0000001 2 ES-0000002 3 ES-0000003 invoice: invoice_id customer_id vat_number ========== =========== ========== 100 1 NULL 101 3 NULL 102 ...
I've recently inherited a website written in PHP and given the responsibility to move it from the current host to our own internal web servers. I succesfully copied the files and i can browse the site on our webserver (IIS7) however some, not all, of the PHP scripts do not appear to execute properly. For example, the following code l...
For example the following query works fine: SELECT * FROM quotes WHERE expires_at <= '2010-10-15 10:00:00'; But this is obviously performing a 'string' comparison - I was wondering if there was a function built in to MySQL that specifically does 'datetime' comparisons. ...
Hello all, Here is my issue, I am selecting and doing multiple joins to get the correct items...it pulls in a fair amount of rows, above 100,000. This query takes more than 5mins when the date range is set to 1 year. I don't know if it's possible but I am afraid that the user might extend the date range to like ten years and crash it....
table Picture with rows have name 1.jpg,2.jpg,3.jpg,4.jpg,5.jpg,6.jpg,7.jpg,8.jpg,9.jpg,10.jpg,11.jpg select * from Picture order by name mysql order : 1.jpg,10.jpg,11.jpg,2.jpg,3.jpg,...... Issue: I want it sort all type name like as with Window 1.jpg,2.jpg,3.jpg,4.jpg,5.jpg,6.jpg,7.jpg,8.jpg,9.jpg,10.jpg,11.jpg and it must worki...
I have a classified website, and I recently changed the insertion of classifieds to use php Session variables instead of alot of POST and FORMS... So first BEFORE changing to Sessions, it worked nice and all special characters showed up correctly. Now that I changed to SESSIONS, I get funny characters instead of the special characters....
I'm having a problem with this query for some reason: $this->db->query("UPDATE schools SET name = '$name', pop = '$pop', details = '$details', numteachers = '$numteachers', recess = '$recess', equipment = '$equipment' WHERE id = '$schoolid'"); echo $this->db->affected_rows(); For some reason affected_rows prints 0 but there's no erro...
I am currently planning a project in which revision safety of the database data is important. This means: we want to be able to proof that the data in the database was not tempered with since it was imported - no user changed the value, no db admin logged into the database and changed it. What is the best way to achieve this? Till now,...
I'm building a web application and I just received some basic foundation data from the client in a spreadsheet. I need to get the data into a MySQL database in a meaningful way so I can effectively query it later. What makes the most sense? Here's the particulars: There are Departments, Classes *edit (classes are types of products like ...
Is it possible to sort a result set by some column and also by RAND()? For example: SELECT `a`, `b`, `c` FROM `table` ORDER BY `a` DESC, RAND() LIMIT 0, 10 Thank you. ...
Is there an application or a code I can use to check which cache functions are turned on? On this app I'm working on, I thought there was mysql cacheing, but since I started using the SELECT SQL_NO_CACHE in one of my queries (applied yesterday), the cacheing has not stopped. This leads me to assume it's a php cache function that's occ...
Hi! How are you doing today? I'm writing because I have a database problem. I am trying to migrate a Grok/Zope application from ZopeDB to MySql, and I don't know exactly how to specify one of the relationships between tables... I am using “megrok.rdb” and “sqlalchemy”. I have four classes involved: A record with some information (l...
Hi Guys, I am trying to create a php script that inputs the HTTP links pasted into a textarea into a separated row in a database. More exactly: First page is where the textarea (name=textarea-linkfield) is, links are going to be pasted here http://www.stackoverflow.com http://www.yahoo.com .... http://google.com The links are being ...
I've written SQL count statements before but I need to write a query which returns 2 sets of count values for a condition. The original query counts the amount of people for a company who have invalid information, fine, works great. I now need to extend this query so that it performs the above operation but also includes the total count ...