Jump to content
Larry Ullman's Book Forums

Help Needed For Further Developed Login Script


Recommended Posts

Hi all.

 

I am running into an issue that I cannot solve with my still limited knowledge. I am developing a login and user page based on the login script of chapter 16.

 

In short, the site is a login part of an oldtimer vehicle site. People can register an account, enter their vehicles and then register for an annual event by selecting vehicles they want to show on that event.

 

I am currently trying to build a page where an admin-level user can print all the registered events for all the users with all the vehicles per event registration.

 

The way we developed it is that the selected vehicles are imploded as a comma delmited line in the event_entries table.

By means of an explode I am able to print all the selected vehicles back into a table.

But only for 1 user.

 

I am definitely using the wrong code, but I am stuck.

I have 3 tables: users, vehicles and event_entries.

 

I would like to print all the registrations for the event of for instance 2012.

So the code should select the first event of 2012, then print all the information including the explosion of the selected vehicles. And then go on with the next event, etc, etc.

 

This is how I exploded the values:

 

while ($row = mysqli_fetch_array($r)) {

$selection = $row['selected_vehicles'];

}

$selection = explode(',', $selection);

// run query of selected vehicles to show in the rest of the form

$q = "SELECT * FROM vehicles WHERE vehicle_id IN ('".join("','", $selection)."')";

$r = @mysqli_query ($dbc, $q);

while ($row = mysqli_fetch_array($r)) {

// echo several of these rows

}

 

Does anyone have any idea how I can repeat this process for all the rows in the database?

 

I hope I make any sense and someone can help me with this limited info.

 

Thanks!

 

Mike

Link to comment
Share on other sites

Presumably users can have many vehicles and enter more than one vehicle in an event and a particular vehicle can be entered into many events so I think you need another table e.g. events which will store the details about the events. Event-entries will store all the vehicles entered into an event. Then you can use a join statement across the tables using an ORDER BY clause on the events table.

  • Upvote 1
Link to comment
Share on other sites

Thanks for your reactions so far!

 

@Margaux. A vehicle is bound to a user, as a user owns it. Even though multiple user can have a similar vehicle, every vehicle is unique. Indeed the vehicle can be used in more entries of the user, but I will limit the visibility of event_entries to the current year only for normal users.

 

The "problem" is that we decided to implode multiple vehicle selection into a comma separated table row. So, if a user selects his vehicle_id numbers, which is generated at the moment the vehicle data is entered (auto-incremental), it will be entered in a row selected_vehicles e.g. 1, 2, 6, 8

 

exploding it for one user works fine, in such a way that it will match the vehicle_id in the vehicles database, and nicely fills out all the vehicle details. But I lack the knowledge to repeat this in a list for all event_entries.

 

@antonio

 

table design:

 

users:

-- phpMyAdmin SQL Dump

-- version 3.4.10.1

-- http://www.phpmyadmin.net

--

-- Host: localhost

-- Generation Time: May 15, 2012 at 03:59 PM

-- Server version: 5.0.95

-- PHP Version: 5.2.9

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

SET time_zone = "+00:00";

 

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

--

-- Database: `oldtimer_regtest`

--

-- --------------------------------------------------------

--

-- Table structure for table `users`

--

CREATE TABLE IF NOT EXISTS `users` (

`user_id` int(10) unsigned NOT NULL auto_increment,

`first_name` varchar(20) NOT NULL,

`last_name` varchar(40) NOT NULL,

`email` varchar(80) NOT NULL,

`pass` char(40) NOT NULL,

`user_level` tinyint(1) unsigned NOT NULL default '0',

`active` char(32) default NULL,

`registration_date` datetime NOT NULL,

`address` varchar(80) NOT NULL,

`zip` varchar(6) NOT NULL,

`por` varchar(50) NOT NULL,

`tel` varchar(15) NOT NULL,

`fax` varchar(15) default NULL,

PRIMARY KEY (`user_id`),

UNIQUE KEY `email` (`email`),

KEY `login` (`email`,`pass`),

KEY `user_level` (`user_level`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

 

--------------------------------------------------------------------------------------------------------------------

 

vehicles:

-- phpMyAdmin SQL Dump

-- version 3.4.10.1

-- http://www.phpmyadmin.net

--

-- Host: localhost

-- Generation Time: May 15, 2012 at 04:02 PM

-- Server version: 5.0.95

-- PHP Version: 5.2.9

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

SET time_zone = "+00:00";

 

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

--

-- Database: `oldtimer_regtest`

--

-- --------------------------------------------------------

--

-- Table structure for table `vehicles`

--

CREATE TABLE IF NOT EXISTS `vehicles` (

`vehicle_id` int(10) NOT NULL auto_increment,

`category` varchar(12) NOT NULL,

`brand` varchar(30) NOT NULL,

`type` varchar(60) NOT NULL,

`year` tinytext NOT NULL,

`user_id` int(11) NOT NULL,

`reg_date_vehicle` datetime NOT NULL,

`details` longtext NOT NULL,

`cilaant` tinytext NOT NULL,

`cilinh` tinytext NOT NULL,

`power` tinytext NOT NULL,

`origin` text NOT NULL,

`prodvol` tinytext NOT NULL,

PRIMARY KEY (`vehicle_id`),

KEY `user_id` (`user_id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

 

 

--------------------------------------------------------------------------------------------------------------------

event_entries:

-- phpMyAdmin SQL Dump

-- version 3.4.10.1

-- http://www.phpmyadmin.net

--

-- Host: localhost

-- Generation Time: May 15, 2012 at 03:58 PM

-- Server version: 5.0.95

-- PHP Version: 5.2.9

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

SET time_zone = "+00:00";

 

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

--

-- Database: `oldtimer_regtest`

--

-- --------------------------------------------------------

--

-- Table structure for table `event_entries`

--

CREATE TABLE IF NOT EXISTS `event_entries` (

`event_id` int(11) NOT NULL auto_increment,

`user_id` int(10) NOT NULL,

`event_year` year(4) NOT NULL,

`selected_vehicles` varchar(100) NOT NULL,

`tour` tinytext NOT NULL,

`parade` varchar(100) NOT NULL,

`sales` tinytext NOT NULL,

`remarks` varchar(1000) default NULL,

`event_reg_date` datetime NOT NULL,

PRIMARY KEY (`event_id`),

KEY `user_id` (`user_id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

-------------------------------------------------------------------------------------------------------------------------

Hope this helps

Link to comment
Share on other sites

I don't understand this line of code

$q = "SELECT * FROM vehicles WHERE vehicle_id IN ('".join("','", $selection)."')";

but I think you will need a couple of nested loops, one to loop through your exploded selected vehicle array.

 

$r = mysqli_query($dbc,"SELECT user_id, selected_vehicles, event_year FROM event_entries ORDER BY event_year DESC");
while ($row=mysqli_fetch_array($r, MYSQLI_ASSOC)){
$selection=$row['selected_vehicles'];
$vehicles=explode(',',selection);
$numVehicles=count($vehicles);
for ($i=0; $i<$numVehicles; i++){
	$r2 = mysqli_query($dbc,"SELECT * FROM vehicles AS v INNER JOIN users AS u USING user_id WHERE v.vehicle_id=$vehicles[i]");
   while ($row2 = mysqli_query($r2,MYSQLI_ASSOC)){
}
	}
}


Link to comment
Share on other sites

Oops, I hadnt finished editing...

 

$r = mysqli_query($dbc,"SELECT selected_vehicles, event_year FROM event_entries ORDER BY event_year DESC");
while ($row=mysqli_fetch_array($r, MYSQLI_ASSOC)){
 $selection=$row['selected_vehicles'];
 $vehicles=explode(',',selection);
 $numVehicles=count($vehicles);
 for ($i=0; $i<$numVehicles; $i++){
 $r2 = mysqli_query($dbc,"SELECT * FROM vehicles AS v INNER JOIN users AS u USING user_id WHERE v.vehicle_id=$vehicles[i]");
 $row2 = mysqli_query($r2,MYSQLI_ASSOC);
 //some code
 } // end for loop
} // end while $row

Link to comment
Share on other sites

Margaux...

 

Thanks I will try the code first thing tomorrow... past midnight here now, and I got square eyes :)!

Will let you know what I found!

 

Thanks again!

Oh... that code $q = "SELECT * FROM vehicles WHERE vehicle_id IN ('".join("','", $selection)."')"; was a piece of code I found online and changed it to my needs. It goes way beyond my knowledge so far, but it seemed to do what I wanted to reach: Print back the vehicle details from the imploded comma separated vehicle_id's

 

Mike

Link to comment
Share on other sites

You might try something like this:

$query = "  SELECT		event_id,
					event_year,
					vehicles.*,
					users.user_id,
					first_name,
					last_name
		FROM		event_entries,
					vehicles,
					users
		WHERE		event.event_year = '2012' AND
					event.user_id = users.user_id AND
					vehicles.vehicle_id IN (event_entries.selected_vehicles) AND
					vehicles.user_id = users.user_id						
		ORDER BY	event.event_year DESC, last_name ASC";

$r = mysqli_query ($dbc, $query);

if ($r) { // no errors in query, begin table
echo <<<EOT

<table>
<tr>
	<th>Event Year</th><th>Exhibitor</th><th>Vehicle</th>
</tr>
EOT;

while ($row = mysqli_fetch_array ($r, MYSQLI_ASSOC)) {
	echo <<<EOT
<tr>
	<td>{$row['event_year']}</td>
	<td>{$row['last_name']}, {$row['first_name']}</td>
	<td>{$row['year']} {$row['brand']} {$row['type']}</td>
</tr>
EOT;
} // end while loop
echo '</table>' . "\n";
} // end $r conditional

You might need to fiddle a bit with the IN (event_entries.selected_vehicles) part.

  • Upvote 2
Link to comment
Share on other sites

Hi Paul, thanks for your answer, but I got Margaux' code to work for me. I had to adjust a bit here and there, but this is the final code I implemented:

 

I got inspired and found the following working based on the query I had already working ok on the page. Some things like the $vehicle[$i] I split, to make it clearer for myself what is going on.

 

$q = "SELECT event_id, selected_vehicles, event_year, DATE_FORMAT(event_reg_date, '%d-%m-%Y') AS vrd, user_id FROM event_entries WHERE user_id=$usrid AND event_year=$evnt_year ORDER BY event_id LIMIT $start, $display";

$r = @mysqli_query ($dbc, $q);

 

while ($row=mysqli_fetch_array($r, MYSQLI_ASSOC)){

echo '<td align="left"><small>' . $row['event_id'] . '</small></td>';

// explode vehicles to brand and type

$selection=$row['selected_vehicles'];

$vehicles=explode(',',$selection);

$numVehicles=count($vehicles);

for ($i=0; $i<$numVehicles; $i++){

$v = $vehicles[$i];

$qv = "SELECT brand, type FROM vehicles WHERE vehicle_id=$v";

$rv = @mysqli_query ($dbc, $qv);

$rowv = mysqli_fetch_row($rv);

echo '<td><small>'. $rowv[0] . ' ' .$rowv[1] . '</td>';

}

 

 

Thanks Margaux and all others!

You're the best

 

Mike

  • Upvote 2
Link to comment
Share on other sites

 Share

×
×
  • Create New...