In PHP+MySQL+PDO, would it be much slower to do Get an item's ID by name Get item data by ID than to just Get item data by name The latter is only one query, so obviously faster. The former makes for cleaner code though (because the item's ID is often also just known beforehand), so it would be better if the performance differenc...
I am trying to create a timesheet and want to have the following SQL Query done: I have the fields datebeginning and dateending and jobid and I want to only add a new one if no open one exists for a given job id and if not I want the query to update the fild dateending for the given id. Is there a way to do that without using PHP to fir...
I'm working on a new CMS to use on repeative projects. Basically this chunk of code connects to a server, grabs all the names of the tables and uses them to generate a simple navigation. It works pretty great for what I need however I'm just wondering if maybe I can optimize this code sniplet even more and make it even more simple. Ma...
$resourcesbuilt = mysql_query("SELECT resourcesbuilt FROM user WHERE username = '$username' LIMIT 1"); if ($resourcesbuilt<=0 ) { mysql_query("INSERT INTO resources (username, dollars) VALUES ( '$username', '1000')") or die (mysql_error()); mysql_query("UPDATE user SET resourcesbuilt = '1' WHERE username = '$username'"); } Basically...
What is main difference between INSERT INTO table VALUES .. and INSERT INTO table SET? Example: INSERT INTO table (a, b, c) VALUES (1,2,3) INSERT INTO table SET a=1, b=2, c=3 And what about performance of these two? ...
I don't really know how to explain this in a understandable way but here goes. The project I'm working on is a web application that revolves around courses, and each course have a set of prerequisites, the problem is that I don't know a good way to present these for the user. Example: To take course4, the person must have sold at least...
I want to write a stored procedure to increment the value of an int column by one. Looks like a very simple task, but not for someone without ANY experience with stored procedures. To do that I looked for tutorials and code samples, and found a few, but sure there are better ones out there. Do you know any? The ones I found: http://d...
This is my code to update a table. My problem is that after submitting a fresh record I'm unable to update the first time (it shows blank), but the second time it works fine. One more thing: when I remove the include statement then it is working fine on submessage.php there is no any phpcode. [annakata: I have no idea what this means] ...
As part of a web application users can upload files of data, which generates a new table in a dedicated MySQL database to store the data in. They can then manipulate this data in various ways. The next version of this app is being written in CakePHP, and at the moment I can't figure out how to dynamically assign these tables at runtime...
I've got a table with the following columns: id int(10) user int(10) winner int(10) profit double created datetime The winner column can be either 0 or 1. I'd like to create a query that returns the maximum number of consecutive winners as ordered by the created datetime column along with the first and last created date as well as th...
Ok guys I've downloaded the wikipedia xml dump and its a whopping 12 GB of data :\ for one table and I wanted to import it into mysql databse on my localhost - however its a humongous file 12GB and obviously navicats taking its sweet time in importing it or its more likely its hanged :(. Is there a way to include this dump or atleast pa...
Hi guys, Got a relatively simple MySQL query that I'm pulling using php with the following code: $employeeNames = mysql_query( "SELECT * FROM employees WHERE team=\"1st Level Technical Support_a\" LIMIT 0,5000") or die(mysql_error()); $employeeNumRows = mysql_num_rows($employeeNames); echo ...
One of my queries on MySQL 5.0.45 is running slow in "sending data" phase. The query is a simple select, returns about 300 integer ID fields as result set. mysql> SELECT source_id FROM directions WHERE (destination_id = 10); +-----------+ | source_id | +-----------+ | 2 | | 8 | ... | 2563 | +-----------+ 341 rows i...
Hi guy i am selecting my active record from my table estates and all my other record are fine but the active record give me errors my code is @query = Estate.find_by_sql"SELECT (e.name) as estate_name, g.name as governing_body,"+ "(select count(*) from stands s where s.estate_id = e.id AND #{filter_es...
I am writing an application and using MySQL to return the difference between 2 dates in MySQL should MySQL do this or should I actually let PHP handle it? I also just need the sum of all the results I am getting back should I return them and add them up on the php side or is there a way to add all the results together on the MySQL Serve...
Hi! I just migrating one of our applications from pure JDBC to Spring's JDBCTemplate. I was wondering how to create a write lock for a table. Do i just execute a "LOCK TABLE foo" Query or is there a generalisized way for doing this in JDBCTemplate? Thanks! ...
For me it is like using hardcoded values instead of constant variables in application code. But there are different opinions out there. So I can't really decide for sure. P.S. For the scope of this question let us assume that performance is not an issue. ...
I am doing some work on an asp website that uses Access as its database. Recently, the site has been having unexplained downtime, and we are wondering if making a whole new site, and switching to PHP with mySQL would bring a big performance boost or not. It is a fairly busy site with about max 80 people connected at once. ...
Hi All Hope you can advise. Im pulling my hair out on a query and hope you can advise. I have a table where I want to select MAX from an inner count between dates with an IN. View Table Here is what I have tried but with no luck at all. SELECT MAX(no_hits) from (SELECT count(hits) AS 'no_hits' ) FROM stats WHERE 'date' >= DATE_SUB(C...
I'm fairly new to mysql and need a query I just can't figure out. Given a table like so: emp cat date amt cum 44 e1 2009-01-01 1 1 44 e2 2009-01-02 2 2 44 e1 2009-01-03 3 4 44 e1 2009-01-07 5 9 44 e7 2009-01-04 5 5 44 e2 2009-01-04 3 5 44 e7 2009-01-05 1 6 55 e...