Barand
-
Posts
24,420 -
Joined
-
Last visited
-
Days Won
805
Posts posted by Barand
-
-
What I need is to be able to get a list of posts in desc order and group them by threadid which then leave me with the the most recnt post for each thread in a descending order.
The code I posted will give the latest post for each thread but not necessarily in desc time order by thread. For that you would need the date field, but as its varchar (and not datetime) I don\'t know if the contents would give the correct sorted sequence.
Edit: just realised we _can_ use postid, I\'ll amend my code and resubmit.
-
Heres the SQL dump I used:-
[php:1:1ca2a8e291]CREATE TABLE forums (
postid int(
unsigned NOT NULL auto_increment,forumno int(
unsigned NOT NULL default \'0\',threadid int(
unsigned NOT NULL default \'0\',date varchar(15) NOT NULL default \'\',
user_id int(
unsigned NOT NULL default \'0\',ip varchar(30) NOT NULL default \'\',
subject varchar(50) NOT NULL default \'\',
comment blob NOT NULL,
deleted int(1) unsigned NOT NULL,
newthread int(1) unsigned NOT NULL,
PRIMARY KEY (postid),
KEY game_id (user_id)
) TYPE=MyISAM;[/php:1:1ca2a8e291]
Will I need to change the structure then?
I\'d strongly advise using a proper datetime type for date fields instead of varchar and also do not call it \'date\' as it\'s a reserved word. Use something like \'post_date\'.
-
Assuming your db does not support subqueries, this will list the latest post for each thread
[php:1:0308db28b1]<?php
$sql = \"SELECT * FROM forums WHERE forumno = \'$board\' ORDER BY threadid, postid DESC \";
$res = mysql_query($sql);
$lastid=0;
while ($row = mysql_fetch_array($res)) {
if ($row[\'threadid\'] != $lastid) {
echo \"$row[\'threadid\'] $row[\'postid\']<br>\";
}
$lastid = $row[\'threadid\'];
}
?>[/php:1:0308db28b1]
hth
-
Remove the comma from between t.location and from.
hth
-
will that keep it constantly updated?
No, its a one time transfer. If you are keeping both tables do it this way
1 Create new table without any auto_increment on uid but keep as primary key
2 Add \'uid\' field to my query and execute
INSERT INTO cont_users (uid, username, password,email) SELECT uid, uname, pass,email FROM nkw_users
3 drop uname, pass and email field from original table (don\'t want duplication.
4. Decide on which is to be the master table (prob the new one) and change to auto_inc on uid and take auto_inc off old table (or vice versa) so only one generates new uid. When adding new users, insert one with auto key first and write record with same key to other table.
5. Now for JOIN bit
To access data from both tables
SELECT * FROM cont_users c INNER JOIN nkw_users n ON c.uid = n.uid
hth
-
Nooo!
Either recreate table completely (losing data) with my original create SQL
mysql_query("CREATE TABLE reviews ( id int auto_increment primary key, review_name VARCHAR(20), basic_plot VARCHAR(80), review VARCHAR(100), email VARCHAR(20), poster_url VARCHAR(20), publisher VARCHAR(20), author VARCHAR(30))");or run alter query on its own - adds field witthout you losing data
ALTER TABLE reviews ADD id int auto_increment primary key
hth
-
You were getting close with that second attempt.
SELECT DISTINCT M.ID FROM tblmessages M LEFT JOIN tblwhoview W ON M.ID=W.MID WHERE W.ID IS NULL
-
INSERT INTO newtable (username, password,email) SELECT
username, password,email FROM oldtable
-
I haven\'t ploughed through every line of that tome you submitted but heres a couple of observations
You are using reserved words as field names, eg Date, which will give sql syntax errors (unless you put backtick araound the `date` in queries).
If you are going to have a date field you are much better off using type DATE or DATETIME and not text.
Having created a primary key on field id, why then go and create two further indexes on the same field? - its just a waste of time and space.
-
dammit all my mysql wont fit
Thank God for that :!:
-
To find the records that are in TableMaster but not in TableEncoding use a LEFT JOIN :-
SELECT m.ProvinceCode, m.CityOrMuni, m.BarangayName
FROM TableMaster m LEFT JOIN TableEncoding e
ON m.ProvinceCode = e.ProvinceCode
AND m.CityOrMuni = e.CityOrMuni
AND m.BarangayName = e.BarangayName
WHERE e.ProvinceCode IS NULL
hth
-
I suppose it comes down to how keywords are decided.
If users enter the keywords relevant to the record at the time it is submitted to the database, and you want searches limited to those keywords, go with the solution of separate indexed table.
If keywords can be anything in the text, go with FULLTEXT.
Does this sound reasonable?
-
As many as you like.
For example, ou might want something like
$[php:1:6b1d98c71d]<?php
datetime = date(\'d m Y H:i\');
$name = $_POST[\'name\'];
$body = \"$name submitted a form at $date\";
?>[/php:1:6b1d98c71d]
hth
-
It\'s easy too get the script that process the submited form to email you when the the data is received and the database updated.
See php mail() function.
-
Best way is to
echo \"$query1\"
to see what is actually being executed. Could be that $id is empty.
-
Have you added the id column to your reviews table yet?
You can do it without recreating the table with
ALTER TABLE reviews ADD id int auto_increment primary key
-
Adding id field
[php:1:63bb55816f]<?php
mysql_query(\"CREATE TABLE reviews (
review_id int not null auto_increment primary key,
review_name VARCHAR(20),
basic_plot VARCHAR(80),
review VARCHAR(100),
email VARCHAR(20),
poster_url VARCHAR(20),
publisher VARCHAR(20),
author VARCHAR(30))\");
?>[/php:1:63bb55816f]
Listing reviews
[php:1:63bb55816f]<?php
$query = \"SELECT review_id, review_name FROM reviews\";
$result = mysql_query($query);
$numrows = mysql_num_rows($result);
print(\"We have <b>$numrows</b> reviews in our database <p>\");
while($row = mysql_fetch_array($result)){
$id = $row[\'review_id\'];
echo \"<A href=\"showreview.php?id=$id\"><b>$row[review_name]</b></A><br>\";
}
?>[/php:1:63bb55816f]
In showreview.php
[php:1:63bb55816f]<?php
$id = $_GET[\'id\'];
$query = \"SELECT * FROM reviews WHERE review_id = $id\"
// display review contents
?>[/php:1:63bb55816f]
hth
-
SELECT p.perm_name, p.perm_desc FROM
(some_perms p INNER JOIN some_role_perms r
ON p.perm_id = r.perm_id)
INNER JOIN some_user_roles u
ON r.role_id = u.role_id
WHERE u.user_id = $userid
hth
-
I think it would be faster where mysql just has to search an index for a word rather than look for a word amongs several. FULLTEXT seems a large overhead for a keyword search given that you want the keywords in a separate table.
-
I\'d do it this way
thetable
---------
TheID, primary key
MemID,
other fields
INDEX on Memid
theKeywords
---------------
TheID,
Keyword
INDEX on TheID
INDEX on Keyword
SELECT TheID FROM theKeywords WHERE Keyword LIKE \'theWord%\'
hth
-
Try it with quotes around \"navn\"
[php:1:5a0db61e95]<?php
print(\"<td bgcolor=\"#EAEAEA\" width=\"250\">Lagt til av: <a href=\"mailto:\" . $adresser[\"mail\"] . \"\">\" . $adresser[\"navn\"] . \"</a></td>\");
?>[/php:1:5a0db61e95]
hth
-
Each property can have many appellations and each appellation can apply to many properties. Same goes for grape varieties
In these cases you need linking tables to provide many to many relationships. Only one classification is required so classification_id can be held in property table with link to classification table.
Can\'t see a way to do attachments in this forum so I\'ll email diagram
-
Take a look at LOAD DATA
-
Have you got an old cookie named \'username\' with value \'Dark-Hawk\' lurking on your PC?
Selecting last replied to topics in a forum
in MySQL Help
Posted
[php:1:5cdcaf3177]<?php
$sql = \"SELECT * FROM forums WHERE forumno = \'$board\' ORDER BY threadid, postid DESC \";
$res = mysql_query($sql);
$lastid=0;
while ($row = mysql_fetch_array($res)) {
if ($row[\'threadid\'] != $lastid) {
$array[$row[\'threadid\']] = $row[\'postid\']<br>\";
}
$lastid = $row[\'threadid\'];
}
?>[/php:1:5cdcaf3177]
We now have array with key = thread and value = lastest postid for thread. Sort to reverse order and display.
[php:1:5cdcaf3177]<?php
arsort ($array);
foreach ($array as $thread=>$post) {
echo $thread $post;
}
?>[/php:1:5cdcaf3177]