[SOLVED] Select all query

[jmr3460]

I have a database with several tables in it I have written a query that I hope will select everything in the database.

$sql = "SELECT * FROM auth_user AS a, banner AS b, contact AS c, friday AS f, monday AS m,
			saturday AS s, sunday AS u, thursday AS h, tuesday AS t, wednesday AS w
			INNER JOIN * USING ( groupid )";

Can someone tell me if this will work? Then I need to know how to call data from this query?

Thanks for any help.

[megaresp]

I have a database with several tables in it I have written a query that I hope will select everything in the database.

$sql = "SELECT * FROM auth_user AS a, banner AS b, contact AS c, friday AS f, monday AS m,
			saturday AS s, sunday AS u, thursday AS h, tuesday AS t, wednesday AS w
			INNER JOIN * USING ( groupid )";

Can someone tell me if this will work? Then I need to know how to call data from this query?

Thanks for any help.

 

Have you tried running the query to see if it works?

 

As for referencing the resulting data, the appropriate manual page is: http://us3.php.net/manual/en/function.mysql-fetch-array.php

 

The comments on this manual page are also very useful.

[jmr3460]

Thanks for the reply. No I have not tried to run it yet. I am still trying to decide the best way to set up my tables. I have changed them about 4 or 5 times. I am just learning about normalizing forms. I am probably complicating this more than it needs to be. Here is the current tables in my database:

Table 1

'contact_id' INT( 3 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
'group_id' VARCHAR( 20 ) CHARACTER NOT NULL ,
'groupname' VARCHAR( 50 ) NOT NULL ,
'area' VARCHAR( 20 ) NOT NULL ,
'NAWS-number' VARCHAR( 15 ) NOT NULL ,
'phone' VARCHAR( 15 )NOT NULL ,
'web' VARCHAR( 255 )NOT NULL ,
group_notes' TEXT NOT NULL

Table 2

location_id' INT( 3 )  UNSIGNED NOT NULL AUTO_INCREMENT ,
group_id' VARCHAR( 20 ) NOT NULL ,
'street' VARCHAR( 50 ) NOT NULL ,
'city' VARCHAR( 30 ) NOT NULL ,
'state' VARCHAR( 2 ) NOT NULL ,
'zip' VARCHAR( 10 ) NOT NULL ,
'map' VARCHAR( 500 ) NOT NULL ,
'location_notes' BLOB NOT NULL

Table 3

'meeting_id' INT( 3 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
'location_id' INT( 3 ) NOT NULL ,
'group_id' VARCHAR( 20 ) NOT NULL ,
'day' VARCHAR( 10 ) NOT NULL ,
'time' TIME NOT NULL ,
'open' TINYINT( 1 ) NOT NULL ,
'format' VARCHAR( 255 ) NOT NULL ,
'meeting_notes` VARCHAR( 255 ) NOT NULL

I think that this meets some form of a normalized database. In my other attempts I had a table for every day of the week. One of my considerations are that some groups meet more that once a day, some meet three times. Is there a way to make the fetch_array data to organize itself by day then by time within each day?

 

Unless I am advised otherwise I think I will keep these tables and start working on my queries. My goal is to produce a list of meetings (by city), then inside each city list groupname (alphabetically), then each group (by day), then by time.  The list should be formatted as follows:

CITY

Groupname

Address (link to map)

Day, Time Format

Day, Time Format

Day, Time Format

Day, Time Format

CITY

Groupname

Address (link to map)

Day, Time Format

Day, Time Format

Day, Time Format

Day, Time Format

 

Thanks for any input or ideas. This is a project still in the development stage so I will be working on my query after entering some data.

[xtopolis]

Read this article: http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

 

It deals with some similar data of yours (storing addresses) and should be useful.

[jmr3460]

Thanks for the link. I tried to read through it and gather all the information. My tables may not be in full compliance. My format column may have to be made into a table unless I only allow one value (it could have multiple values).

 

I have a query that works with one record in it. I am interested in making this script work in a foreach condition. There will be about 300 records in this database after I am finished with it, and as time goes on there will be multiple changes.

This is the code I have now, and as I stated earlier it is a work in progress:

<?php
ini_set ("display_errors", "1");
error_reporting(E_ALL);
session_start();
//id names of database and table to use
$db_name = "arscnaor_groupinfo";
//connect to server and select database
$connection = @mysql_connect("localhost", "username", "password")
     or die(mysql_error());
$db = @mysql_select_db($db_name, $connection) or die(mysql_error());

$sql = "SELECT contact.groupname, location.street, location.city, location.state, location.zip FROM contact, location WHERE contact.group_id = 'cleanslate'";

$meeting = mysql_query($sql) or die(mysql_error());
$result = mysql_fetch_array($meeting);
$groupname = $result['groupname'];
$street = $result['street'];
$city = $result['city'];
$state = $result['state'];
$zip =  $result['zip'];

echo "<b>" . $groupname . "</b><br/>";
echo $street . "<br/>";
echo $city . "<br/>";
echo $state . "<br/>";
echo $zip . "<br/>";
?>

My next little project will be to call a query that is structured with all the information in a manor so I can work some PHP with it then I will consider this topic to be solved and I will go to the PHP Help if I have any problems with that.

 

I will read the article some more I may need to look at my tables again though. One thing I will look at is the meetings table is the only real table where records will be deleted. The format if I decide to make it a table will also have to be deleted as well for that record. Where does this come into play with the normalization process. I really don't know if I want to try and remember to delete two records. One thing I am thinking is if it works out there will be group members changing their own group information and if they delete the wrong record I will need to come up with a process to bring that action back. I am thinking of creating another table for deleted records of the meetings table. Then if there is a mistake it will just be a matter of reinserting the data back instead of trying to recreating the record.

[xtopolis]

Hey,

Trying to answer the questions that I saw:

 

1)Making this work as a foreach (loop)

-Without knowing a little bit more about your tables, I can't give an example.  It's pretty easy though, just select the data correctly in MySQL so that it comes out per row, and loop through it, just like any normal loop.  Without having your structure and some sample data, it's hard to guess at how your tables might JOIN together.

 

2)Deleting records

-First, unless it's sensitive data, we don't really "delete" records.  Common practice is to mark them as "deleted" and not show them, but to not actually delete them from the database.  I was a little confused about the deleting things in two places, but there are database types which enforce things like this (InnoDB) and you should manage it with a script that does it for you, so you don't have to remember anything.

[jmr3460]

Hey thanks for replying again. My databases are just like the above tables. This database will contain data where a town could have two or more meeting a week. There may even be more that one meeting per day usually at different times. I was hoping to loop through the towns in alphabetical order. Within each town loop I was wanting to loop through each group in alphabetically as well then each group that has meetings at different times I wanted to loop through chronologically from earliest to latest. I believe I can call the variables individually (I think). My question is do I use MySQL to do the alphabetical loops and the loop within the (group and time) or is there a PHP function that will help me with this? This link would be what I am looking to do. Now I have to hard code the information and it is prone to mistakes.

http://www.arscna.org/handheld/ I am hoping to be able to use forms to update and eventually allow group members do their own updating.

 

As far as not deleting. Are you saying not to move data to another table just create another table called display where a field called display is a Boolean that switched on will display and turned off will not which would also have a group_id field in it.

 

I hope this post is not too wordy. Thanks for your help so far. I am really grateful.