Posts by smantscheff

Your outermost loop lacks a sort clause.
I assume that you want an order like this, so that the thread with the youngest reply or topic creation timestamp is displayed first:

TOPIC 1 (timestamp 1)
  REPLY 7 (timestamp 7)
TOPIC 3 (timestamp 3)
  REPLY 4 (timestamp 4)
  REPLY 5 (timestamp 5)
TOPIC 2 (timestamp 2)

It might be easier if you would not separate topics and replies in different tables but to pack all posts belonging to a thread into the same table - the first post, too.

Yes.

Your table structure is fine.
MySQL does not sort records without a SORT BY clause. If you want the result ordered by some criteria, you have to name them explicitly in your query.

If you don't see an error message that means that there probably hasn't been an error.
How would you generate any output? Not in your sample code above.

Change line 3 to or die(mysql_error()) which will tell you about the problem: You have to separate the assignment by comma:

UPDATE anime_list SET anime_name = '$animename', rating = '$rating', episodes = '$episodes' WHERE id='$id'

You cannot reduce the number of queries.
You need a subset of questions, but for each question a complete set of choices.
You cannot do the following because mysql doesn't allow limits in subsets:

select * from questions q, choices c
where q.question_id = c.question_id
and q.question_id in 
(select question_id from questions x where cat_id=3 order by rand() limit 10);

You could first isolate the subset of questions in a separate table:

drop table if exists tmp_questions;
create temporary table tmp_questions like questions:
insert into tmp_questions select * from questions where cat_id=3 order by rand() limit 10;
select * from tmp_questions q, choices c where q.question_id = c.question_id;

You could use INSERT IGNORE.
First INSERT IGNORE into your user table, then into the item table, then in tableA. Then update tableA. You can be sure that the correct data are in it afterwards:

INSERT IGNORE INTO user values('x');
INSERT IGNORE INTO item values(1);
INSERT IGNORE INTO tableA values('x',1,'z');
UPDATE tableA set value='z' where userid='x' and itemid=1;

This is a bit redundant on the query side, but easy to code and to maintain.

This does not make sense. Which field do you want to update on duplicate keys? There is no field named "value" in your table.

Please show the CREATE TABLE statement. IS (userid, itemid) a unique key?

Of course: take me. Or have a look at getafreelancer.com

Run your prepared statement from the mysql command line which will tell you what went wrong.

Use the replace function:

update theTable set theField = replace( theField, '±', '±');

Obviously your interfaces got mixed up with UTF-8 and other encodings.

Learn about preg regular expressions.
Your regular expressions are malformed.
Learn about debugging. Configure your server or your script (with error_reporting(E_ALL) so that it gives you the appropriate error message Warning: preg_match() [function.preg-match]: Unknown modifier ';' in x.php on line y As long as you search for exact strings only, you better use strpos instead of preg_match.

The following construct does the redirect:

if (preg_match( '~iPad;~', $_SERVER['HTTP_REFERER'])) {
  header( "Location: http://$mySpecialIpadSite" );
  exit;
}

Refine the preg expression and substitute your site for $mySpecialIpadSite.

First of all this looks like really bad database design, storing records of different types in the same table.
That said, go along the line (assuming mysql as your database):

$select = '<select>';
$result = mysql( "select * from code there type_cd = '2'" );
while ($row = mysql_fetch_object( $result )) {
  $select .= "<option value='row->id'>$row->description</option>";
}
$select .= '</select>';

and insert the $select variable in your form.

Debug all variables of line 31. Probably you'll find that $to contains two domain names - one which the user entered and one ('example.com') which your program added.

I don't have a clue, but if this was my problem I would dump the database content and all configuration files and look for the function name. Maybe this provides a hint.
The mysql manual says that you get into trouble if your server runs with the option --skip-grant-tables (http://dev.mysql.com/doc/refman/5.0/en/udf-compiling.html). Maybe you activated this switch during debugging and it is still active?

The solution is in the last post of mwasif.

Since you explicitly asked for more advice, here it is:
Write in English, not in amputated abbreviations.
Submit complete test cases with CREATE TABLE statements, INSERT data statements and the queries which are problematic. That way others can just paste and copy your test case into mysql and analyze the problem.
Do not abbreviate table names, field names or variable names. Make them meaningful.
When working with databases, solve as much as you can on the database level and only the rest with the display interface. Use the command line mysql to test and refine your solutions.

PHP has an interface which submits SQL queries to MySQL exactly as you would enter them in the mysql command line interface or any other interface I know of - because that is the language MySQL is understanding.
The PHP interface has no database abstraction but is a (nearly) 1:1 translation of the MySQL API.
Put your query in a PHP text string or a variable and submit it with the mysql() or the mysql_query() function.

You are confusing your table names. You should try it with more meaningful ones. Having "table" as table name is not especially helpful.
That said, try it with
SELECT * FROM ofrd_subjects as subjects, fnsh_table as students WHERE students.Subj_No = subjects.Subj_No and remarks = 'passed'

Yes, this way you can order results by any scalar values (even float and double). You can also combine ASC and DESC orders for different result fields.

SQL does not support the concept of "first" and "last" records or a natural record order. All ordering and sorting has to be done explicitly.
Since you use an auto_increment field you can sort your query result by this field for the desired result:

select * from news order by id desc;

But the fact that you don't know this leads me to recommend that you first take a basic mysql primer course before venturing on real database work. Could save you a lot of time.

I'm referring to the command line tool mysql, not to mysqladmin or any GUI. Command line mysql seems to be the only tool around which does not filter any messages you might want to see - in this case the output of "SHOW CREATE TABLE"

Are you using the mysql command line interface? If not, you should.

Recreate the table before each run with a complete "CREATE TABLE" statement.
Or tell mysql:

alter table news auto_increment = 0;

Have a look at the output of "SHOW CREATE TABLE news" which will make things clearer.

Give it at least a try. Set up some SQL code which shows the unfiltered data. Then add the filter as a WHERE clause.
Relate the tables like this:

SELECT * from ofrd_subjects as subjects, fnsh_table as students WHERE students.Subj_No = subjects.Subj_No

Is it a prerequisite that you use unreadable abbreviations as table names?

Of course. Replication is a mechanism to spread a master database to several slave locations. If you can establish establish a connection between your remote and your local machine, then you can set up replication.

The code from the mysql prompt goes like

load data infile "george.csv" into table george fields terminated by "\t";

See http://dev.mysql.com/doc/refman/5.1/en/load-data.html

@priteas: No, I only remember dimly to have read it on the mysql website itself that LOAD DATA is the fastest upload operation.

@priteas: As far as I know you're wrong. CSV import is always the fastest with MySQL. If there are foreign keys, foreign key checking should be disabled during import for speed.

MySQL supports replication. Have your web database as the master and replicate it to your home server - which means in effect that only the changes are transferred to the replica.
Or try the poor man's version: run your web database with a text log and play it from time to time into your local installation.

Yes of course. MySQL is not aware of the OS it's running on.

How does Drupal allow for "multiple databases"? How does Drupal make use of different databases? Or do you mean that the Drupal database does not have to be on the same server as the PHP scripts? (Which is undoubtely true.)

Since you can code your own plugins/extensions in Drupal, you're completely free to access any accessible MySQL DB using PHP.
Or do you mean: Can Drupal be installed using this database on which also your vb app runs? AFAIK it can. Using its own table prefixes it can install in any database you point at.

Change the line $sql_result = mysql_query($sql,$connection); to $sql_result = mysql_query($sql,$connection) or die(mysql_error()); which will tell you more about the cause.
Since I do not see any syntactical errors I assume that you got either the database name or the table name or some field names wrong. Try select * from directory as your query.
Check your spelling (also upper/lowercase).
And why do you output "&lt;" instead of "<"? It's all ruining your html.

LOAD DATA cannot execute any functions on the data.
Create a temporary table with a text field instead of a data field. Import the data into the temporary table. Then add a date field and convert your text field contents into it.
The date_format function does not work on text fields, only on dates. To insert a value into the date field use the format Y-m-d h:m:i.

Or do the conversion first on the input data with a sed script (or another text conversion tool) which converts your date field in the Mysql date format.

Also try

show errors;
show warnings;

immediately after the "LOAD DATA". You might see some useful debugging info.

Eliminate the ORDER BY clause from your select statement.
Or add an auto_increment field to your table and ORDER BY this field.

You can import CSV files with the mysql command line client with the LOAD DATA statement (http://dev.mysql.com/doc/refman/5.1/en/load-data.html). Put the CSV file on your server so that you won't have network timeouts.
What does it mean that the entries are not "in sequence"? How do you test the sequence? Please show the CREATE TABLE statment for the table in which you want to import.

Try a HAVING instead of a WHERE clause:

SELECT a, Count(a) AS CountOfa1  FROM (SELECT Table1.a, Table1.b, Table1.c, Count(Table1.a) AS CountOfa FROM Table1 GROUP BY Table1.a, Table1.b, Table1.c) GROUP BY a
HAVING CountOfa1 > 1;

I don't understand what you're aiming at. Please provide some sample data and the desired result.

You are mistaking the PHP function parameter for a MySQL input value.
If the PHP value for $email is null, than your query reads: insert into user (username, email) values('somename', ''); because PHP will replace the null value by an empty string.
If you change this query to insert into user (username, email) values('somename', NULL); the database field email will be set to NULL instead of ''.
That's what my str_replace(...) is for.

You can prepare your insert statement in PHP and then replace all empty strings '' by NULL:

$query="insert into user (username, email) values('$username', '$email')";
$query = str_replace( "''", "NULL", $query );

Please mark this thread as solved.

Show the output of your SHOW CREATE TABLE statement and the piece of code where you try to insert something.

drop table if exists accounts;
create table accounts 
(id integer, account integer, fiscalyear integer, openingbalance float );
insert into accounts values 
('1','1202','2011','1122.00'),
('2','1210','2011','0.0'),
('3','1920','2011','1212.33');

drop table if exists transactions;
create table transactions (id integer, account integer, date date, amount float);
insert into transactions values 
('1','1210','2010-01-01','23.00'),
('2','1210','2010-01-23','566'),
('3','1920','2010-01-21','456'),
('4','1202','2010-01-14','956');

select a.account, fiscalyear, openingbalance, openingbalance + sum(amount) as closingbalance, 
sum(amount) as difference
from accounts a, transactions t
where a.account=t.account and year(date)+1 = fiscalyear
group by a.account

+---------+------------+----------------+------------------+------------+
| account | fiscalyear | openingbalance | closingbalance   | difference |
+---------+------------+----------------+------------------+------------+
|    1202 |       2011 |           1122 |             2078 |        956 |
|    1210 |       2011 |              0 |              589 |        589 |
|    1920 |       2011 |        1212.33 | 1668.32995605469 |        456 |
+---------+------------+----------------+------------------+------------+

You need the number of rows to check if your query had an emtpy result or not. And you need to check it to give your user an error message if the input value was invalid.

I don't know the C interface but assume it's the same as PHP's. If so, you could use the mysql_num_rows(res) result, or you could first check the row count by select count(*) from table3 where ID = %d

SELECT CONCAT(ifnull(PropertyRef, ''), ' ', ifnull(SaleType, ''), ' ',ifnull(UnitNo, ''), ' ', ifnull(PropertyType, ''), ' ',ifnull(ProductName, ''), ' ',ifnull(Development, ''), ' ', ifnull(Country, ''), ' ',ifnull(PriceofProperty, '')) AS PropertyRef, PropertyID, ProductTypeID FROM properties

Do you have any reason to mistrust this error message? Delete the last closing bracket where the syntax error occurs.