[PHP-DB] Selecting Alphabetically
Hi, Could anyone point me in the right direction with this one please. I have a table of records wit a field of artists. I want to select all the records which begin with the letter a-h then i-p etc What is the best way to do this? Cheers, Jord -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Most Read Article
Hi, I'm trying to get the most read article out a table. The table has a title column, a read column and some others. So I wanna SELECT the title from the table and the most read figure. I tried: SELECT title, MAX(reads) FROM articles This only gets the second most read article?! If I take out the title like: SELECT MAX(reads) FROM articles Then it gets the top article number but obviously not the title. Can anyone shed some light? Cheers, Jord -- Jordan Elver Web Developer The InternetOne UK Ltd -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] delete user but keep the article
Well I suppose that if you had a lot of users in your users table editing and adding articles then there could be a performance hit, but I don't know about this. Maybe someone else hasmore experince. How many users were you planning on? HTH, Jord On Wednesday 15 August 2001 05:42, you wrote: > Thanks for replying, Jordan. > > > So when I want to delete a user, in fact, > > I just set the status of that user to be dormant so that I can still get > > the > > > user information for displaying the article. Workls if your not going to > > have > > > loads of users. > > but what if i have loads of users? and why can't i use this approach? > > rudy -- Jordan Elver Web Developer The InternetOne UK Ltd -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Printing out usernames and logins
Doh, stupid me. Thanks very much for your help. Your a life saver.
Cheers,
Jord
On Monday 14 May 2001 8:49 am, you wrote:
> ¥es, U R overwriting Ur result-set from the 1st qry.
>
> Store instead the 1st result into an array and iterate
> it then, sending your second select. U can also use 2
> connections, but ... ?
>
> Greetinx,
> Mike
>
> Michael Rudel
> - Web-Development, Systemadministration -
> ___
>
> Suchtreffer AG
> Bleicherstraße 20
> D-78467 Konstanz
> Germany
> fon: +49-(0)7531-89207-17
> fax: +49-(0)7531-89207-13
> e-mail: mailto:[EMAIL PROTECTED]
> internet: http://www.suchtreffer.de
> _______
>
> > -Original Message-
> > From: Jordan Elver [mailto:[EMAIL PROTECTED]]
> > Sent: Monday, May 14, 2001 7:35 PM
> > To: PHP DB List; PHP General List
> > Subject: [PHP-DB] Printing out usernames and logins
> >
> >
> > Hi,
> > I'm trying to print out a list of usernames and the times
> > they logged in.
> >
> > I want to print it out like:
> >
> > joe
> > fred
> > frank
> >
> > Then when you click on one of the names it show just there
> > login times, like
> > this, so if I click on fred it prints out:
> >
> > joe
> > fred
> > -
> > -
> > -
> > frank
> >
> > But it doesn't continue printing out the rest of the
> > usernames after it's
> > finished printing freds logins.
> >
> > Any ideas would be appreciated.
> >
> > The code seems to have something to do with the second query,
> > I think
> >
> >
> > The code I'm using is below.
> >
> > Thanks,
> >
> > Jord
> >
> > db_connect();
> > $sql = "SELECT id, username FROM users";
> > $result = @mysql_query($sql);
> >
> > if(@mysql_num_rows($result) > 0) {
> >
> > echo'This is a list of Members who have Logged In to
> > their account with
> > times and dates:';
> >
> > while(list($id, $username) = mysql_fetch_array($result)) {
> >
> > echo" > HREF=\"$PHP_SELF?user=$username&id=$id\">$username\n";
> >
> > if($username == $user) {
> >
> > $sql = "SELECT UNIX_TIMESTAMP(time) AS time FROM
> > user_logins
> > WHERE user_id = $id ORDER BY time DESC";
> > $result = @mysql_query($sql);
> >
> > while(list($time) = mysql_fetch_array($result)) {
> > echo date('h:i a l d F', $time)."\n";
> > }
> > }
> > }
> >
> > } else {
> > echo error('No one has Logged In yet!');
> > }
> >
> > --
> > PHP Database Mailing List (http://www.php.net/)
> > To unsubscribe, e-mail: [EMAIL PROTECTED]
> > For additional commands, e-mail: [EMAIL PROTECTED]
> > To contact the list administrators, e-mail:
> > [EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Printing out usernames and logins
Hi,
I'm trying to print out a list of usernames and the times they logged in.
I want to print it out like:
joe
fred
frank
Then when you click on one of the names it show just there login times, like
this, so if I click on fred it prints out:
joe
fred
-
-
-
frank
But it doesn't continue printing out the rest of the usernames after it's
finished printing freds logins.
Any ideas would be appreciated.
The code seems to have something to do with the second query, I think :-(
The code I'm using is below.
Thanks,
Jord
db_connect();
$sql = "SELECT id, username FROM users";
$result = @mysql_query($sql);
if(@mysql_num_rows($result) > 0) {
echo'This is a list of Members who have Logged In to their account with
times and dates:';
while(list($id, $username) = mysql_fetch_array($result)) {
echo"$username\n";
if($username == $user) {
$sql = "SELECT UNIX_TIMESTAMP(time) AS time FROM user_logins
WHERE user_id = $id ORDER BY time DESC";
$result = @mysql_query($sql);
while(list($time) = mysql_fetch_array($result)) {
echo date('h:i a l d F', $time)."\n";
}
}
}
} else {
echo error('No one has Logged In yet!');
}
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Checking query suceeded
Hi, If I'm doing more than one query on a page what is the best way to check if they all succeeded with out using transactions? TIA, Jord -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Retrieving and Printing Categories
Hi, I've got a load of records that are in different categories. What is the best way to get all the records or selected records and print them in such a way that they are grouped (on the page) in their relevent category, like: Fruit -> Apples -> Pears -> Bananas Vegetables -> Carrots -> Cabbages etc, etc. Sometime my categories are ina dfferent table, don't know if this matters? How can I do this? TIA, Jord -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Selecting Dates
Hi, I'm trying to select records based on dates. I have a table with dates in the format 2001-04-08 and I'm using the query: SELECT name, description, date_time FROM events WHERE YEAR(date_time) = 2001 AND MONTH(date_time) = 04 AND DAYOFMONTH(date_time) = 08 But it doesn't yield any records? I don't really understand why? It seems to be the last bit 'DAYOFMONTH(date_time) = 08' which cause a problem because if I leave it out of the query, it selects all records for a particular month in a particular year as expected. Cheers, Jord -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Timestamps VS Date/Time Types
Hi, I'm getting a bit confused about what date types to use in my applications and MySQL db. I have used timestamps and I know how to format them, convert them and compare them etc but now I am about to build a calender type script and I'm not sure what to use. For example, if I insert a load of calender appointments into the table, how would I sleect all the events in a certain month (if the dates were timestamps) ? What are the advantages of timestamps over other date//time fileds and vice versa? Thanks for any help, Cheers, Jord -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Passing Arrays
Hi, How can I pass an array between two pages. I've tried using serialize and unserialize. But it doen't return an array. When I use gettype() on it, it say's that the typ-e is boolean? Any ideas? Cheers, Jord -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] mysql_result()
Thanks for everyones help with this one, all suggestions appreciated. Cheers, Jord On Wednesday 04 April 2001 17:06, you wrote: > Jordan, > > If you know your result is going to product one row, try using: > > $row=mysql_fetch_array($result, MSQL_ASSOC); > // returns an assoc array where the field names are keys, field value is > value > > $id=row[id]; > $name=row[name]; > etc. > > Best regards, > Andrew > -- > Andrew Hill - OpenLink Software > Director Technology Evangelism > eBusiness Infrastructure Technology > http://www.openlinksw.com > > -Original Message- > > > From: Jordan Elver [mailto:[EMAIL PROTECTED]] > > Sent: Wednesday, April 04, 2001 11:46 AM > > To: PHP Database Mailing List; PHP General Mailing List > > Subject: [PHP-DB] mysql_result() > > > > > > Hi, > > If I knnow that a query will only retrun one row, can I do thiss (below) > > rather than using a while loop for one record? > > > > $id = @mysql_result($result, 0, 'id'); > > $name = @mysql_result($result, 0, 'name'); > > $email = @mysql_result($result, 0, 'email'); > > $address1 = @mysql_result($result, 0, 'address1'); > > $address2 = @mysql_result($result, 0, 'address2'); > > $town_city = @mysql_result($result, 0, 'town_city'); > > $postcode = @mysql_result($result, 0, 'postcode'); > > > > Cheers, > > > > Jord > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > For additional commands, e-mail: [EMAIL PROTECTED] > > To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] mysql_result()
Hi, If I knnow that a query will only retrun one row, can I do thiss (below) rather than using a while loop for one record? $id = @mysql_result($result, 0, 'id'); $name = @mysql_result($result, 0, 'name'); $email = @mysql_result($result, 0, 'email'); $address1 = @mysql_result($result, 0, 'address1'); $address2 = @mysql_result($result, 0, 'address2'); $town_city = @mysql_result($result, 0, 'town_city'); $postcode = @mysql_result($result, 0, 'postcode'); Cheers, Jord -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Best way to check if a query succeeded
Hi,
i was just wondering what you guys do to check if a wquery suceeded or not?
I know about mysql_num_rows() and mysql_affected_rows(), just wondered what
you guys do?
I normally do something like:
$sql = "SELECT something FROM table";
$result = mysql_query($sql);
if(@mysql_num_rows($result) > 0) {
echo'Query Suceeded';
} else {
echo'Query failed';
}
Cheers,
Jord
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: AW: [PHP-DB] Structure Question
Thanks for your advice. I think I'll go with that. Cheers, Jord On Tuesday 27 March 2001 15:10, you wrote: > I think you should store all files in a table with an extra table with the > filetypes (like you suggest in the end of your mail)!! In my opinion this > is a well designed database modell, 'cause you have only one table (instead > of 10 or twelve depending on the count of your datatypes), but you are able > to select over the filetyp criteria!! > > Stefan Siefert > > -Ursprüngliche Nachricht- > Von: Jordan Elver [mailto:[EMAIL PROTECTED]] > Gesendet: Dienstag, 27. März 2001 14:38 > An: PHP Database Mailing List > Betreff: [PHP-DB] Structure Question > > > Hi, > I want to keep filenames of different kinds of media(real sudio, qt, mp3) > for > articles in a db table. Would it be best to store each kind of media in a > different table like one table for real audio, one for quicktime, one for > mp3. Or, would it better to store them all in the same table but with > another > table telling me what kind of file it is? > > Like: > > media table > > id | name | filename | type_id > > media types > > type_id | name > > Any ideas folks? > > Cheers, > > Jord > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Structure Question
I am. Jord On Tuesday 27 March 2001 14:06, you wrote: > Why don't you just store the file names in a db? > > iets much easyer > > > Hi, > > I want to keep filenames of different kinds of media(real sudio, qt, mp3) > > for > > > articles in a db table. Would it be best to store each kind of media in a > > different table like one table for real audio, one for quicktime, one for > > mp3. Or, would it better to store them all in the same table but with > > another > > > table telling me what kind of file it is? > > > > Like: > > > > media table > > > > id | name | filename | type_id > > > > media types > > > > type_id | name > > > > Any ideas folks? > > > > Cheers, > > > > Jord > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > For additional commands, e-mail: [EMAIL PROTECTED] > > To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Re: Betr.: [PHP-DB] Structure Question
I thought that it was good db design to have no repeating data in your db but to use a relationship instead? Jord On Tuesday 27 March 2001 14:42, you wrote: > Jord, > > I don't see quite why you should want to use two tables, you can easily put > all the information in one table and have one column which holds the > filetype. There's (almost) no speed profit to put the information in > several tables. > > Have fun, > Maarten Verheijen > > >>> Jordan Elver <[EMAIL PROTECTED]> 27-03-01 13:37 >>> > > Hi, > I want to keep filenames of different kinds of media(real sudio, qt, mp3) > for articles in a db table. Would it be best to store each kind of media in > a different table like one table for real audio, one for quicktime, one for > mp3. Or, would it better to store them all in the same table but with > another table telling me what kind of file it is? > > Like: > > media table > > id | name | filename | type_id > > media types > > type_id | name > > Any ideas folks? > > Cheers, > > Jord -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Structure Question
Hi, I want to keep filenames of different kinds of media(real sudio, qt, mp3) for articles in a db table. Would it be best to store each kind of media in a different table like one table for real audio, one for quicktime, one for mp3. Or, would it better to store them all in the same table but with another table telling me what kind of file it is? Like: media table id | name | filename | type_id media types type_id | name Any ideas folks? Cheers, Jord -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] Query - Grouping Results
Sorry, the query works fine, but how do I print it out to the browser so that it looks like: John Dates go here Bob Dates go here Gill Dates go here I hope that's clear, Thanks, Jord On Monday 19 March 2001 14:44, you wrote: > works fine for me. > what error do you get. > > > -Original Message- > From: Jordan Elver [mailto:[EMAIL PROTECTED]] > Sent: Monday, March 19, 2001 2:39 PM > To: Jeff Armstrong > Cc: PHP Database Mailing List; PHP General Mailing List > Subject: Re: [PHP] Query - Grouping Results > > > Doesn't seem to work, how would I print that out with PHP? > > On Monday 19 March 2001 13:52, you wrote: > > how about something like > > select distinct > > name, > > date_format(time, "%W %D %M %Y") as login > > from > > users, user_logins > > where > > user_logins.user_id = users.id > > order by name,time > > > > > > -Original Message- > > From: Jordan Elver [mailto:[EMAIL PROTECTED]] > > Sent: Monday, March 19, 2001 1:43 PM > > To: PHP Database Mailing List; PHP General Mailing List > > Subject: [PHP] Query - Grouping Results > > > > > > Hi, > > I've got a table like: > > > > id user_id ip time > > 1 2 127.0.0.1 20010316105018 > > > > Etc, etc. > > > > I do a join on the this table and the users table to get the coresponding > > username to user_id like this: > > > > SELECT users.name AS name, user_logins.ip AS ip, > > UNIX_TIMESTAMP(user_logins.time) AS time FROM users, user_logins WHERE > > user_logins.user_id = users.id ORDER BY time ASC > > > > How can I display the results grouped by username? > > > > So, I want to be able to display: > > > > Logins for John > > > > Thursday 10th > > Friday 12th > > Monday 23rd > > > > Logins for Bob > > > > Monday 1st > > Tuesday 2nd > > Saturday 31st > > > > Thanks for any help, > > > > Jord -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Re: [PHP] Query - Grouping Results
Doesn't seem to work, how would I print that out with PHP? On Monday 19 March 2001 13:52, you wrote: > how about something like > select distinct > name, > date_format(time, "%W %D %M %Y") as login > from > users, user_logins > where > user_logins.user_id = users.id > order by name,time > > > -Original Message- > From: Jordan Elver [mailto:[EMAIL PROTECTED]] > Sent: Monday, March 19, 2001 1:43 PM > To: PHP Database Mailing List; PHP General Mailing List > Subject: [PHP] Query - Grouping Results > > > Hi, > I've got a table like: > > iduser_id ip time > 1 2 127.0.0.1 20010316105018 > > Etc, etc. > > I do a join on the this table and the users table to get the coresponding > username to user_id like this: > > SELECT users.name AS name, user_logins.ip AS ip, > UNIX_TIMESTAMP(user_logins.time) AS time FROM users, user_logins WHERE > user_logins.user_id = users.id ORDER BY time ASC > > How can I display the results grouped by username? > > So, I want to be able to display: > > Logins for John > > Thursday 10th > Friday 12th > Monday 23rd > > Logins for Bob > > Monday 1st > Tuesday 2nd > Saturday 31st > > Thanks for any help, > > Jord -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Query - Grouping Results
Hi, I've got a table like: id user_id ip time 1 2 127.0.0.1 20010316105018 Etc, etc. I do a join on the this table and the users table to get the coresponding username to user_id like this: SELECT users.name AS name, user_logins.ip AS ip, UNIX_TIMESTAMP(user_logins.time) AS time FROM users, user_logins WHERE user_logins.user_id = users.id ORDER BY time ASC How can I display the results grouped by username? So, I want to be able to display: Logins for John Thursday 10th Friday 12th Monday 23rd Logins for Bob Monday 1st Tuesday 2nd Saturday 31st Thanks for any help, Jord -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Login System with access levels
Hi, I've got a db with a username and password in it. I can let people log in, like SELECT * FROM table WHERE username = username AND password = password. But how can I add an access level column so that I can have different levels of security. So admin's can read everything, but users can only read certain sections. How could I add to my db and structure a query? Any ideas would be good, Cheers, Jord -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Sessions in Functions
Hi,
I have some code which I decided to make into a function. Some of the code
updates a session var which holds the current time. It now does not work.
// update session variable with new time
session_register("LOGGED_IN['time']");
$LOGGED_IN['time'] = mktime();
Are there issues that I should be aware of when I use sessions like this?
Thanks,
Jord
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Text Searches on MySQL
Hi, I'm putting together a db with articles in it. Would it be better to have two tables with the main text of the articles in it linked to a main article table or just chuck it all in together in one table. I want to be able to do a full text search on the articles. Any help please, Cheers, Jord -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Ordering Articles
Hi, I'm trying to build an article management system. Basically it will have normal artcile type things, a picture or two etc. The articles will be ordered by date by default, but I want to include the option of being able to order the articles if need be. i.e. say I add more artciles but i still want one of the older artIcles to be at the top of the list, how would I do that? I hope this is clear, Cheers, Jord -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] DB Abstraction
Hi, I thought it was about time I started using a db abstraction class. Problem is, there are so many out there that I don't which one to start using? I've heard of ADODB (I think). Anyone have any suggestions? Cheers, Jord -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
