mysql

PHP PDO PASSWORD HASH?

In a basic mysql insert you are able to set a password variable 'PASSWORD($password)' but this breaks a PDO statement. How do you hash the password while using pdo::prepare and pdo::execute? $sql= "INSERT INTO contractors (userid, password, name) VALUES ('$userid', '$pass1', '$name')"; $result = $dbh->prepare($sql); $count = $result-...

Using temporary, using filesort a bad idea in mysql?

Hi all. I am trying to optimize my mysql queries to avoid 'using temporary, using filesort'. I could use some help. First; here is the explain Here is the Query select pf.*,m.login,m.avatar from profile_friends pf, members m where pf.friend_id = m.id and pf.member_id = 16586 order by m.lastLogin desc limit 0,24; mysql> EXPLAIN s...

Optimizing MySQL Query, takes almost 20 seconds!

Hi all, I'm running the following query on a Macbook Pro 2.53ghz with 4GB of Ram: SELECT c.id AS id, c.name AS name, c.parent_id AS parent_id, s.domain AS domain_name, s.domain_id AS domain_id, NULL AS stats FROM stats s LEFT JOIN stats_id_category sic ON s.id = sic.stats_id LEFT JOIN cate...

how do you Use the "With" Clause in MySQL

I am converting all my MSSQL queries to mysql and my queries that has "with" in them are all failing with t1 as ( SELECT article.*, userinfo.*, category.* FROM question INNER JOIN userinfo ON userinfo.user_userid=article.article_ownerid INNER JOIN category ON article.article_categoryid=category.catid WHERE article.ar...

Foreign key constraints while dumping data

mysqldump --compact --no-create-info -h192.168.150.180 -uroot -p live pnlbus_stops | sed s/pnlbus_stops/bus_stops/g | mysql test I am getting an error: ERROR 1062 (23000) at line 1: Duplicate entry 'AN' for key 1 This is because bus_stops table in the test DB has foreign key constraints. How do I truncate the bus_stops table from test...

MySQL Insert & Joins

i saw this In MySQL, joins work for INSERT, UPDATE, and DELETE statements. It's possible to change data in more than one table when joining tables in an UPDATE or DELETE statement. in an answer for a question from the mysql certification guide. is it true? inserts with joins? an example of it? ...

How to structure some data in database for easy retrieval later

I have a set of checkboxes that an Admin can tick to give users privleges. In my case, the Admin can click these checkboxes: Image on ImageShack. These checkboxes will allow users to view data for those countries/cities. I am trying to rack my brains to do the folowing: 1) How to best insert this permissions into my database 2) How t...

URL Shortening Site

I am working on a URL shortening site which uses PHP, MySQL and Apache. General idea of URL shortening as I look at open source projects: user gives a URL link and the system gets ID for that link from database. Then convert the ID X base system (I am using base 36). Then use Apache mod_rewrite and create shortened URL and then redirect....

Update one table with newly inserted id from another

So, the situation is this: Table Foo has a foreign key to table Bar. There are currently many rows in Foo where this foreign key is null. All Foos need a Bar. I want to: For each Foo where bar_fk is null, insert a new row into Bar. Update Foo with the newly added id for Bar. How would you do this in MySQL? Can it be done using st...

Date Difference in mysql

hey all, i have a problem regarding date diff function of mysql, i can use it and it is simple but i dont understand that how do i use it to collect difference within the table field. e.g. i have a column "dob" and i want to write a query that will do something like select dateDiff(current_timeStamp,dob) from sometable 'here dob is the...

Can't insert from subsonic 2.2 to mysql (Joomla CMS database)

I can't insert record into mysql database (joomla CMS table jos_content). Receive: Exception:"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use Any idea, what could be wrong, since I'm just copying all data except autoincremental from source record (same dat...

joins in mysql5

I've seen people writing the following query SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name written like this SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name2.column_name =table_name1.column_name does it actually make any difference? ...

Alternative formulation

Hi, Is there a way that this query can be re-arranged as a composite query or not? SELECT motor_car, COUNT(engine_type) FROM ford_cars GROUP BY motor_car HAVING COUNT(engine_type) >= ALL (SELECT COUNT(engine_type) FROM ford_cars GROUP BY motor_car); What the query is trying to achieve is to give the motor ...

What happens if I connect to a utf8 MySQL DB table using latin1?

Interesting question... if I have a MySQL table with CHARSET=utf8, and I open a connection with latin1 encoding, what happens? I tried this, and even characters such as ß and æ could be stored and retrieved properly. Those characters are represented with different byte sequences in utf8 and in latin1, so I didn't expect it to work. Is ...

Sub Query possibility instead of using JOIN ?

Hi, Is it possible to write the code below using a sub query and not use JOIN statement? SELECT Nurse_no, Nurse.Name, Auxilary_No, Patient.Name FROM Nurse LEFT OUTER JOIN Patient ON Nurse_no = Agency_Nurse_No; Any help is grately appreciated. Ben ...

How to make a proper mysqli extension class with prepared statements?

I have scoured the web for a good example but I can't find anything. I am trying to extend the mysqli class to make a helper class that will abstract away some of the complexities. One of the main things I want to accomplish is to make use of prepared statements. I don't really know where to start, or how to handle input and output p...

way to search entire database for a string in MySQL

I am working on a MySQL database that is huge (about 120 tables). I am trying to make some sense of it and it will help a great deal if I can search all 120 tables + columns for a string I am looking for. Is that possible to do on a MySQL DB? ...

How can I use OAuth and PHP without using the oauth-php library?

I need to build a script that uses OAuth authentication to connect to Foursquare's API, but oauth-php library is too complicated. Any one page scripts that don't use MySQL? I would rather just use cookies. ...

MySQL UTF Text Capacity

Hi, I have column named "summary" (tinytext, utf8_turkish_ci). I know it can store 255 byte also 255 chars. But if i use unicode chars like Ç, Ö, Ü storage capacitiy naturally decreasing. If my visitor enter 250 chars long unicode text then last chars are erasing. My summary column will be always 250 chars long. What can i do ? Thanks. ...

How to Use Regexp in MySQL Replace Commands?

Hi, My goal is to replace the links in the database with a catchall link. I generally use the REPLACE command for replacing string in the database, but this time I am having difficulty because in order to find the links I need to use regular expressions, and that is simply not working out: UPDATE node_revisions SET body = REPLACE ( `bo...