mysql

How to count between two MySQL tables using MySQL and PHP?

For example, how can I count how many times the tag HTML is displayed in the a_id column and then display all the tags that are related to the a_id column in alphabetical order. Here is the MySQL tables CREATE TABLE a_tags ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, tag_id INT UNSIGNED NOT NULL, a_id INT UNSIGNED NOT NULL, PRIMARY KEY (...

How to install MySQL as a service with options.

I want to install the MySql service with the --old-passwords flag Iv tried running this and various permutations of it, but cant get it to work. "c:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-nt" --install MySQL -defaults-File="pathtofile" --old-passwords ...

Problem with returning rows using PHP

<?php if (isset($_GET['flyerID'])) $FlyerID = $_GET['flyerID']; $DBConnect = @mysqli_connect("host", "UN", "pword") Or die("<p>Unable to connect to the datbase server.</p>" . "<p>Error Code ".mysqli_connect_errno().": ".mysqli_connect_error()) . "</p>"; $DBName = "agentsleuthdb"; @mysqli_select_db($DBConnect, $DBName) O...

Auto converting data from a datetime field to a specific format in CakePHP

Hi is possible automatically to format (using date()) all data from a datetime field in CakePHP? I'm thinking about using a callback function in the model but I don't know if I could filter fields coming from a datetime type. Thanks in advance! ...

How do you know when your mysql commands have been executed?

I'm very new at MySQL and I'm trying to execute some commands, wait a certain period of time until they are uploaded and then check to ensure that the data from the commands is actually in the table so I can delete these files. Is there any sort of first priority/instant way of executing these sort of commands? A way of setting the max...

SQL - Select Fields of the Max ID

The last InvoiceID, and corresponding fields needs to be selected. The entire sql contains several inner joins. SELECT max(InvoiceID), InvoiceEndDate FROM Invoices WHERE TransactionOrderItemID = '000831' Right now, I am getting the InvoiceID, and have to fetch the InvoiceEndDate again. Is there an efficient way of d...

MySQL get rows but prefer one column value over another

A bit of a strange one, I want to write a MySQL query that will get results from a table, but prefer one value of a column over another, ie id name value prioirty 1 name1 value1 NULL 2 name1 value1 1 3 name2 value2 NULL 4 name3 value3 NULL So here name1 has two entries, but one has a prioirty of 1. I wan...

How to display text bigger according to how many times entered into a MySQL database using PHP and MySQL?

Okay, I have this script that should display the tags that are entered more times bigger and tags that are entered less smaller for a certain question. But for some reason it displays the last tag entered bigger and displays all the tags that where entered before it smaller like if it was counting down. I need to fix this problem. I hop...

Get total memory usage for constructing a result set from a query

Is there a way to tell how much total memory MySQL used to construct a result set from a single query? What I'm looking for is something similar to php's memory_get_peak_usage(). I have a complex query that produces a few rows of summed data. The actual result set is tiny -- 6 or 8 rows of a floating decimal value. But the query I use ...

Confusing output from time...

This command gets some data from MySQL and then manipulates it. Why is the 'real' time so much higher than the 'user' time? >time ./command.rb real 45m45.457s user 3m36.478s sys 0m28.226s For clarification - I understand the difference between the real, user, and sys output. However, I am confused at why there is such a gre...

MySQL Table with TEXT column

Hello. I've been working on a database and I have to deal with a TEXT field. Now, I believe I've seen some place mentioning it would be best to isolate the TEXT column from the rest of the table(putting it in a table of its own). However, now I can't find this reference anywhere and since it was quite a while ago, I'm starting to think...

How would I do this JOIN in Rails?

Here's my SQL statement: SELECT * FROM `message_users` LEFT JOIN `messages` ON message_users.message_id = messages.id WHERE (message_users.user_id = 1 AND message_users.hidden = 0) AND message_users.last_read_at > messages.updated_at ORDER BY messages.updated_at DESC LIMIT 0, 20 How would I pull that off with proper ...

Can mysql_pconnect be called multiple times in one php page?

I was just wondering if it is valid to call mysql_pconnect multiple times in one php page? Also, if it is possible do I have to somehow close my last mysql handle or something? Basically is the following code ok? Or lacking some other steps? mysql_pconnect("ip:3306", "user", "pass") or die(mysql_error()); mysql_select_db("Test") or die(...

Is it possible to insert data in MySQL from a SQL Server trigger?

A fun question for a Friday afternoon, no? As the question states, is it possible to write a SQL Server trigger that inserts data into a MySQL table? EDIT: Yes, I know this is basically a dumb idea and that the application should be doing it and so on. But this is a legacy Classic ASP application which we're transitioning to MySQL; a...

Select statement using WHERE x IN (SELECT ...)

I have two tables: [ product_to_category --------------------- product_id category_id [ category --------------------- category_id parent_id I need to get all product_id's with a category.parent_id of '39'. Here's what I'm trying, but it's returning empty when there are at least a few hundred: SELECT product_id FROM product_to_cate...

MySQL indexing strategy

I am trying to use the following query on a table with ~200k records in it. There are all sorts of other fields that can be filtered by, but this is a base example. SELECT b.isbn FROM books b WHERE b.price IS NOT NULL AND b.deleted = '' AND b.publication_date <= '2009-12-04' AND ( b.subject1_id IN ('CAT1','CAT2','CAT3','CAT4','C...

PHP MySQL fails whenever WHERE statement is present

I am trying to make a simple query to a small MYSQL table, but when I insert the Where clause, I suddenly get an invalid query error (added a while(mysql_fetch_array ){} when working without the where). The MYSQL console gives a 1064 (syntax) error, however, I checked the MYSQL documentation and I am using the proper syntax as far as I c...

Convert UPDATE with INNER JOIN from SQL for use in MySQL

I'd like to convert this for us in MySQL: UPDATE product SET price = 12.95 FROM product INNER JOIN product_to_category ON product.product_id = product_to_category.product_id INNER JOIN category ON product_to_category.category_id = category.category_id AND category.parent_id = 39 MySQL doesn't like the FROM portion...

Are Prepared Statements a waste for normal queries? (PHP)

Nowadays, "Prepared statements" seem to be the only way anyone recommends sending queries to a database. I even see recommendations to use prepared statements for stored procs. However, do to the extra query prepared statements require - and the short time they last - I'm persuaded that they are only useful for a line of INSERT/UPDATE qu...

Best way to return search info from a database in php

So I have a form for people to fill out, which is below... Once they fill that form out, how can I query it and return the information from my database? <form name="form" method="get" action="agents.php"> <table> <tr> <td width = "20%">Last Name: </td> <td><input type="text" name="LASTNAME" size="20"/> ...