Jump to content

[SOLVED] Where Statement Help... maybe?

Clinton

By Clinton
in MySQL Help

 Share

Recommended Posts

Clinton Regular Member

Clinton

Posted
Posted

Ok, this is somewhat confusing and I'm not sure how to go about it.

 

Whenever I get a new product it is entered into the database. The database creates two new tables based on the date of the product. One table contains general information about the table, such as whether or not the table has been closed (no longer used) and the other table maintains records, such as when somebody checks the product out or in.

 

So right now I have about 20 tables in my database.

 

I want to create a master inventory list. Basically I want this list to echo all the tables that are not 'closed' and then I want it to list out all of the records that are in each table. This is going to be many pages long but I need to be able to do this.

 

Each product is separated into one of 8 categories so I generally search by category and then whether or not the table has been closed:

 

$result = mysql_query("SELECT * FROM masterlist WHERE producttype = 'Det Cord 50 Grain' AND closed = '' ORDER BY datecode");

 

I would need to do the same thing but after I get this result I would need to find the tables with the matching names and then show all the information in those tables.

 

I would imagine a where statement would be involved but I'm not exactly sure how to do this. I'm confused. :-|

Link to comment
Share on other sites
GingerRobot Regular Member

GingerRobot

Posted
Posted

Perhaps it's just me, but im not sure what the question/problem is.

 

I'd also have serious concerns about your data structure if you're making new tables all over the place. Perhaps if you were to tell us what information you need to store and your current structure, we might be able to suggest a more appropriate method.

Link to comment
Share on other sites
Clinton Regular Member

Clinton

Posted
  • Author
Posted

Ok. Here it is.

 

Attached:

 

Masterlist shows how I keep track of each individual tables and whether or not they are still active (closed).

TableExample shows how I keep track of who checks what in and out.

Result is a picture of what is outputted.

 

Here is what happens when somebody adds a new product to database:

 

<?php
session_start();

$page = $_POST['page'];
$datecode = $_POST['datecode'];
$producttype = $_POST['producttype'];
$productdescription = $_POST['productdescription'];
$purchasedfrom = $_POST['purchasedfrom'];
$docid = $_POST['docid'];

if ( @$_SESSION['login'] == "yes" AND @$_SESSION['released'] == "0" )
{

$_SESSION['location'] == $location;

$tablename = "$datecode$producttype";

$username = "
$password = "
$hostname = "

$dbhandle = mysql_connect($hostname, $username, $password)
or die("Unable to connect to MySQL");

$selected = mysql_select_db("..........)
or die("Can not open the Inventory Database. Please consult your local dialing directory or try your call later.");

$sql = "INSERT INTO masterlist (datecode, producttype, productdescription, purchasedfrom, docid) VALUES ('$datecode', '$producttype', '$productdescription', '$purchasedfrom', '$docid')";
echo $sql;
$result = mysql_query($sql) or die(mysql_error());

$sql2 = "INSERT INTO weights(tablename) VALUES ('$tablename')";
echo $sql2;
$result2 = mysql_query($sql2) or die(mysql_error());

$sql3 = "CREATE TABLE `$tablename` (id int(255) NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), datecode varchar(255), producttype varchar(255), transactiondate date, joblocation varchar(255), jobnumber varchar(255), signout varchar(255), signin varchar(255), outinby varchar(255))";
echo $sql3;
$result3 = mysql_query($sql3) or die(mysql_error());


?>

<html>
<head>
<meta http-equiv='refresh' content='0;url=<? echo $page; ?>.php' />
<title></title>
</head>


<body bgcolor="#FFFFFF">

</body>


</html>


<? }

else if ( @$_SESSION['login'] == "yes" AND @$_SESSION['released'] == "1" )
{
echo "Your previous access has been revoked. <meta http-equiv='refresh' content='3;url=h
";
}
else
{
echo "You have tried to enter a Employee Area only. Please login. 
";
}


mysql_close($dbhandle); ?>

 

Here is the code for the RESULT:

 

<?php
session_start();

if ( $_GET['choice'] == "")
{$choice = $_POST['choice'];}
else
{$choice = $_GET['choice'];}

if ( $_GET['type'] == "")
{$type = $_POST['type'];}
else
{$type = $_GET['type'];}

$table = "$choice$type";

if ( @$_SESSION['login'] == "yes" AND @$_SESSION['released'] == "0" )
{

$_SESSION['location'] == $location;

$username = "
$password = "
$hostname = "

$dbhandle = mysql_connect($hostname, $username, $password)
or die("Unable to connect to MySQL");

$selected = mysql_select_db("c$dbhandle)
or die("Can not open the Inventory Database. Please consult your local dialing directory or try your call later.");

$result = mysql_query("SELECT * FROM masterlist WHERE producttype = 'Det Cord 50 Grain' AND closed = '' ORDER BY datecode");

?>

<html>
<head>
<script type="text/javascript" src="calendarDateInput.js"></script>
<link rel="stylesheet" type="text/css" href="bannermenu.css" />
<title><? echo $location; ?> Master Inventory Control</title>
</head>


<body>

<div id="banner">
<h1><? echo $location; ?> Master Inventory Control<em>- 50 Grain Det Cord</em></h1>
</div>

<ul id="tabmenu">
<li><a href="index.php">Home</a></li>
<li><a class="active" href="50grain.php">50 Det Cord</a></li>
<li><a href="100grain.php">100 Det Cord</a></li>
<li><a href="unigel.php">Unigel Dynamite</a></li>
<li><a href="unimax.php">Unimax Dynamite</a></li>
<li><a href="lpnonel.php">LP NONEL</a></li>
<li><a href="msnonel.php">MS NONEL</a></li>
<li><a href="electric.php">Electric</a></li>
<li><a href="shocktube.php">Shock Tube</a></li>
<li><a href="totals.php">Totals</a></li>
</ul>


<p>   </p>
<div id="content">
<?php

if ($choice=="")

{
echo "<center>";

echo "Select the appropriate date code to view/edit: \n";

echo "<form action='' method='post'>\n";

echo "<select name='choice' value=''>Date Code</option>\n";

while($nt=mysql_fetch_array($result))
{

echo "<option value='{$nt['datecode']}'>{$nt['datecode']}</option>\n";
}

echo "</select>\n";
echo "<INPUT type=hidden name=type value='Det Cord 50 Grain'>\n";
echo "<input type='submit' value='Select'>\n";
echo "</form>";

echo "</center>";
}

else

{
$result = mysql_query("SELECT * FROM masterlist WHERE producttype = 'Det Cord 50 Grain' AND closed = '' AND datecode = '$choice'");

while($nt=mysql_fetch_array($result))
{extract($nt);

echo "<center><a href='printrecord.php?type=$type&choice=$choice'>Print Record</a></center>";

echo "<table border=1 align=center><tr><td><table> <tr><td align=right><b><font color=blue>PRODUCT TYPE:</b></font></td><td>$producttype </td></tr><tr><td align=right><b><font color=blue>PRODUCT DESCRIPTION:</b></font></td><td>$productdescription </td></tr><tr><td align=right><b><font color=blue>PURCHASED FROM:</b></font></td><td>$purchasedfrom </td></tr><tr><td align=right><b><font color=blue>DOC #:</b></font></td><td>$docid</td></tr></table></td></tr></table>";

}

echo "<center><h1><b>Date Code:</b> $choice</h1></center>";



$sumadd = mysql_query("SELECT datecode,SUM(signin),SUM(signout) FROM `$table` GROUP BY `datecode`") or die(mysql_error());

while($row = mysql_fetch_array($sumadd)){

$totaladd = $row['SUM(signin)'];
$totalsubtract = $row['SUM(signout)'];

$grandtotal = ($totaladd - $totalsubtract);



echo "<center>Amount in Magazine = <font color=purple>$grandtotal</font> </center>";

}

$username2 = "

$password2 = "

$hostname2 = "



$dbhandle2 = mysql_connect($hostname2, $username2, $password2)

or die("Unable to connect to MySQL");



$selected2 = mysql_select_db("clintona_products",$dbhandle2);



$powder = mysql_query("SELECT new FROM master WHERE name = '$type'") or die(mysql_error());



while($sp = mysql_fetch_array($powder)){



$new = $sp['new'];



$totalgrams = ($grandtotal * $new);

$totalpounds = ($totalgrams / 453.59237);



echo "<center> Weight in Magazine = <font color=purple>"; echo round($totalpounds, 4); echo "</font> lbs";

}





echo "<p> ";

echo "<table border=1 align=center width=80%>";

echo "<tr><td><b><u><font
color=blue><center>Date:</center></font></u></b></td><td><b><u><font
color=blue><center>Job
Location:</center></font></u></b></td><td><b><u><font
color=blue><center>Job
#:</center></font></b></u></td><td><b><u><font
color=blue><center>Out:</center></font></u></b></td><td><b><u><font
color=blue><center>In:</center></font></u></b></td><td><u><b><font
color=blue><center>In/Out
By:</center></font></u></b></td></tr>";



$username = "

$password = "

$hostname = "



$dbhandle = mysql_connect($hostname, $username, $password)

or die("Unable to connect to MySQL");



$selected = mysql_select_db("clintona_".$location."Inventory",$dbhandle)

or die("Can not open the Inventory Database. Please consult your local dialing directory or try your call later.");

////////////////////////////////UPDATE WEIGHT///////////////////////////////////////

$sql2 = "UPDATE weights SET currentweight = '$totalpounds' WHERE tablename = '$table'";
$result2 = mysql_query($sql2) or die(mysql_error());

//////////////////////////////SHOW POWDER TABLE///////////////////////////////////

$result3 = mysql_query("SELECT * FROM `$table` ORDER BY `transactiondate`");



while ($row2 = mysql_fetch_array($result3))

{extract($row2);

echo
"<tr><td><center>$transactiondate</center></td><td><center>$joblocation</center></td><td><center>$jobnumber</center></td><td><center><font
color=red>$signout<font></center></td><td><center><font
color=green>$signin</font></center></td><td><center>$outinby</center></td></tr>";

}

echo "</table>";

////////////////////////////////////// Delete a Record ////////////////////////////////////////////



echo "<center>To close out this datecode check the box and then hit submit.</center>";



echo "<form name='hide' action='close.php' method='post'>
Delete:<input type='checkbox' name='close' value='1'>
                                        
<input type='hidden' name='type' value='$type' /><input
type='hidden' name='choice' value='$choice' /><input
type='hidden' name='page' value='50grain' /><input type='submit'
value='Delete!'></form>";







////////////////////////////////////// How To Insert a Record ////////////////////////////////////////////



echo "<p> <p>";



echo "<p> <p>";



echo "<h2><center><u> How to Insert a Record: </u></h2>";



echo "When inserting a record enter it in one of three ways:<p>";



echo "<table
border=1><tr><td></td><td><center>Date</td><td><center>Job
Location</td><td><center>Job
#:</td><td><center>Out</td><td><center>In</td><td><center>In/Out
By:</td></tr>";



echo "<tr><td>How to Sign Powder <font
color=green><b>IN:</b></font></td><td><center>2008-06-06</td><td><center>Rio
Bravo
Rocklin</td><td><center>8321-A55</td><td><font
color=red><center>LEAVE
BLANK!</font></td><td><center>50</td><td><center>CJA</td></tr>";



echo "<tr><td>How to Sign Powder <font
color=red><b>OUT:</b></font></td><td><center>2008-06-06</td><td><center>Rio
Bravo
Rocklin</td><td><center>8321-A55</td><td><center>50</td><td><center><font
color=red>LEAVE
BLANK!</font></td><td><center>CJA</td></tr>";



echo "<tr><td>How to <font
color=purple><b>INVENTORY:</b></font></td><td><center>2008-06-06</td><td><center>Inventory</td><td><center>N/A</td><td><center><font
color=red>LEAVE
BLANK!</font></td><td><center><font
color=red>LEAVE
BLANK!</font></td><td><center>CJA</td></tr>";



echo "</center>";



echo "</table>";



////////////////////////////////////////// Insert a Record ///////////////////////////////////////////////



echo "<p> <p>";



echo "<p> <p>";



echo "<h2><center><u> Insert a Record: </u></h2>";



echo "Be careful as records <font color=red>CANNOT</font> be deleted<p>";



echo "<form action='insert.php' method='post'>";



echo "<input type='hidden' name='tablename' value='$table' />";



echo "<input type='hidden' name='datecode' value='$choice' />";



echo "<input type='hidden' name='producttype' value='$type' />";



echo "<input type='hidden' name='page' value='50grain' />";



echo "<input type='hidden' name='choice' value='$choice' />";



echo "<input type='hidden' name='type' value='$type' />";



echo "<script>DateInput('transactiondate', true, 'YYYY-MM-DD')</script>";



echo "Job Location: <input type='text' name='joblocation'/>";



echo "Job #: <input type='text' name='jobnumber'/>";



echo "Out: <input type='text' name='signout'/>";



echo "In: <input type='text' name='signin'/>";



echo "Out/In By: <input type='text' name='outinby'/>";



echo "<br><input type='submit' value='Insert!'>";



echo "</form>";

}



?>

</div>



</body>



<? }



else if ( @$_SESSION['login'] == "yes" AND @$_SESSION['released'] == "1" )

{

echo "Your previous access has been revoked. <meta h

}

else

{

echo "You have tried to enter a Employee Area only. Please login

}



?>

 

 

 

[attachment deleted by admin]

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.