mysql

Mysql count rows function

I have created a simple function which takes user id and show total no. of post by the user id function totalpost($user_id){ $sql = 'SELECT * FROM posts WHERE u_id='.$user_id; $total = mysql_num_rows(mysql_query($sql)) or die(mysql_errno()); return $total; } Its working fine, but when 0 record found, it not returns anything. I want ...

PHP echo xyz if rows in loop contain no data

I am trying to echo some text if my loop returns with no data but can't get it do work. I have tried a few things but no luck. my code: $result = mysql_send("SELECT * FROM datatable WHERE id='". $_SESSION['id']."'ORDER BY id ASC LIMIT 2"); while($row=mysql_fetch_array($result)) { $a = 1; extract($row); echo 'Trans ID:...

Generate table schema inspecting Excel(CSV) and import data

How would I go around creating a MYSQL table schema inspecting an Excel(or CSV) file. Are there any ready Python libraries for the task? Column headers would be sanitized to column names. Datatype would be estimated based on the contents of the spreadsheet column. When done, data would be loaded to the table. I have an Excel file of ~2...

MySQL Event Scheduler on a specific time everyday

here's my query CREATE EVENT reset ON SCHEDULE AT TIMESTAMP DO UPDATE `ndic`.`students` SET `status` = '0'; how can i update status to "0" at 1 pm every day i mean what should i put instead of TIMESTAMP? ...

MySQL Wildcards * and %

What is the difference between % and * wildcards in MySQL? In a query like so : "SELECT * FROM $table WHERE MATCH (message) AGAINST('$string*' IN BOOLEAN MODE)" ...

Trying to build a dynamic PHP mysql_query string to update a row and getting back the updated row

I have a form that jQuery tracks the onChage .change() event so when something is changed it runs a ajax request and i pass in the column, id, and the values in the url. Here i have the PHP code that should update the data. My question is now how do i build the mySQl string dynamically. and how do i echo back the changes/updates that w...

How do I add values to a an array model in doctrine ?

Hi, I'm using Doctrine with Codeigniter, I defined my ratings column as $this->hasColumn('ratings','array', 1000);. I'm using $res = Doctrine::getTable('Resource')->find($resource_id); $res->ratings = $rating; $res->save(); but only the $rating gets inserted, overwriting the last value, I want to store the ratings as an...

Sort MySQL result set using comparison between 2 columns of same value type

Hello, I have a table containing last_updated_1 and last_updated_2 columns, used respectively for text and images update time on a post. I wish I could get a result set of 10 rows based on all time last updated records contained in the 2 columns, ex. row 1 = last_updated_1 record, row 2 = last_updated_2 record, row 3 = last_updated_1 r...

SQLAlchemy: Inserting the results of a query into another table

So I have some results which I've got from the install table, like so: install = metadata.tables['install'] results = session.query(install) #<sqlalchemy.orm.query.Query object> I'd like to insert these same results into the install_archive table. I'm not entirely sure how to do this, because I don't want to duplicate the schema b...

Is this SQL select code following good practice?

I am using SQLite and will port to MySQL (5) later. I wanted to know if I am doing something I shouldn't be doing. I tried purposely to design so I'll compare to 0 instead of 1 (I changed hasApproved to NotApproved to do this, not a big deal and I haven't written any code). I was told I never need to write a subquery but I do here. My ...

how to get insert sql in mysql shell or java library?

my table is: [tableAbc] A B C ------------ 1 a b 2 c e 3 e r ... run: show create table tableAbc; to get the create table sql but how to get insert sql? like: insert into `tableAbc` ('A','B','C') VALUES(1,'a','b'); ... any idea? (or any java library to do this) thanks all! BTW: i want show the "insert sql" to web brower. so...

Compare two String with MySQL

Hi, I wan't to compare two strings in a SQL request so I can retrieve the best match, the aim is to propose to an operator the best zip code possible. For example, in France, we have Integer Zip code, so I made an easy request : SELECT * FROM myTable ORDER BY abs(zip_code - 75000) This request returns first the data closest of Paris....

Can I connect to a 64 bit mysql server from a 32 bit machine with 32 bit mysql client lib?

Can I connect to a 64 bit mysql server from a 32 bit machine with 32 bit mysql client lib? I mean the server is 64 bit version and running on an 64 bit machine and the client app is running on an 32 bit machine with the 32 bit mysql client lib. Is that OK? Or it will cause some problems? I don't have two machine so I can't do the test...

Migrating .NET/MySQL app. to DB2

I want to migrate my existing desktop Windows .NET 3.5 app. that uses MySQL as backend to DB2 Express C. I downloaded IBM Migration Toolkit to migrate the database. I have further queries: How accurate is IBM Migration Toolkit? Are there any issues? For the application, just changing the connection string will do or something else? ...

How to archive changes to table rows?

I have a MySQL database where I am storing information that is entered from a PHP web page. I have a page that allows the user to view an existing row, and make changes and save them to the database. I want to know the best way to keep the original entries, as well as the new update and any subsequent updates. My thought is to make a ...

mysql update table from another table

Hi, I'm trying to update a field in one table, from the sum of another field, in another table. company_tbl (PRIMARY, companySize, companyName) location_tbl (PRIMARY, companyID, locationSize, locationName) The two tables link by company_tbl.PRIMARY = location_tbl.companyID update company_tbl comp, location_tbl loc set companySize = su...

MySQL Table Alias

I'm migrating an application from a case insensitive database to MySQL. My DBA does not permit changing any Mysql configuration settings so using the "lower_case_table_names" is not an option. Does MySQL have any aliasing ability to have multiple table names point to the same table? For example, if USER and usersB were aliased to the s...

Help optimizing a query for MySQL

I have a MySQL query that goes as follows SELECT count(`clicks`.`user_id`) as total, `users`.`fullname` FROM `users`, `clicks`, WHERE `users`.`id` = `clicks`.`user_id` GROUP BY `clicks`.`user_id` ORDER BY `total` desc LIMIT 0,20; I am running statistics on several button pressing type games. It has a us...

Writing JPEG image from BLOB field

Hi, I've got a BLOB field in a MySQL database that I need to fetch and write into a .jpg file. I already know how to query the database and get the result, but I can't write it into a valid .jpg file. Anyone got any help? Thanks ...

SELECT statement for a VIEW in MySQL not returning all the wanted rows

I need help with my MySQL query: CREATE OR REPLACE DEFINER = CURRENT_USER VIEW users_phpscheduleit AS SELECT u.uid AS memberid, pass AS password, mail AS email, pv1.value AS fname, pv2.value AS lname, pv3.value AS phone, e_add, e_mod, e_de...