PHP - Getting The Id From A Mysql Table?

Hello,

So I need a bit of help with tables. I currently have my website set up with the following tables:

products (productId, productName, link, productPrice, productDesc)

productscents (scentId, scentName, scentDesc)

salves (productId, productName, productDesc)


So now most products in the products table can be made in any of the scents in the productScents table (except for the salves and reed diffuser jar).  I have a separate table of the salve types that can be ordered. How do I join each product with each productScent.

For example:

If I have the following products with their Id number:
1 Body Mist
2 Massage Oil
3 Reed Diffuser Jar


and the following scents with their id (id has leading zeros):
0001 Mango
0002 Passionfruit
0003 Grapefruit

How would I create a master products table that will display the following:
3        Reed Diffuser Jar
10001 Mango Body Mist
10002 Passionfruit Body Mist
10003 Grapefruit Body Mist
20001 Mango Massage Oil
20002 Passionfruit Massage Oil
20003 Grapefruit Massage Oil


I hope this makes sense. lol.

Any help would be greatly appreciated.

Hi,   The following code was written by someone else. It allows me to upload images to a directory while saving image name in the mysql table.   I also want the code to allow me save other data (surname, first name) along with the image name into the table, but my try is not working, only the images get uploaded.   What am I missing here?

if(isset($_POST['upload']))
{

$path=$path.$_FILES['file_upload']['name'];

if(move_uploaded_file($_FILES['file_upload']['tmp_name'],$path))
{
echo " ".basename($_FILES['file_upload']['name'])." has been uploaded<br/>";
echo '<img src="gallery/'.$_FILES['file_upload']['name'].'" width="48" height="48"/>';
$img=$_FILES['file_upload']['name'];
$query="insert into imgtables (fname,imgurl,date) values('$fname',STR_TO_DATE('$dateofbirth','%d-%m-%y'),'$img',now())";
if($sp->query($query)){
echo "<br/>Inserted to DB also"; 
}else{
echo "Error <br/>".$sp->error; 
}
}
else
{
echo "There is an error,please retry or check path";
}
}

?>

joseph

Hi, what I am trying to do is "update" a mysql table via a form post, instead of "insert into" a table.   I have a selected ID field or RID in the case of my code below...

Code: [Select]
<?php

$q=$_GET["q"];

$con = mysql_connect('my-host', 'my_user', 'my_pwd');
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_db", $con);

$sql="SELECT *
      FROM daterange
  WHERE DEND > DATE(NOW())
  AND STATUS='A' AND MONTH = '".$q."'
      ORDER BY RID, DATE, SITE";

$result = mysql_query($sql);


// Determine the number of reservation dates
$number = mysql_numrows($result);


// Create drop-down menu of reservation dates
print "<font size=\"3\" face=\"Arial\"><b>Select Your Reservation:</b><br>
<form action=\"resersend.php\" method=\"post\">
<select name=\"RID\">
    <option value=\"\">Choose One</option>";

for ($i=0; $i<$number; $i++) {
     $RID = mysql_result($result,$i,"RID");
     $DATE = mysql_result($result,$i,"DATE");
     $SITE = mysql_result($result,$i, "SITE");
 $PRICE = mysql_result($result,$i, "PRICE");
     print "<option value=\"$RID\">$DATE, $SITE, $PRICE</option>";
}

print "</select><p align=left><label><font size=\"3\" face=\"Arial\">First Name: <input type=\"text\" name=\"FNAME\" size=\"50\" maxlength=\"50\" tabindex=\"1\"<br>";

print "<p align=left><label>Last Name: <input type=\"text\" name=\"LNAME\" size=\"50\" maxlength=\"50\" tabindex=\"2\"<br>";

print "<p align=left><label>Address Line 1: <input type=\"text\" name=\"ADDR1\" size=\"50\" maxlength=\"50\" tabindex=\"3\"<br>";

print "<p align=left><label>Address Line 2: <input type=\"text\" name=\"ADDR2\" size=\"50\" maxlength=\"50\" tabindex=\"4\"<br>";

print "<p align=left><label>City: <input type=\"text\" name=\"CITY\" size=\"50\" maxlength=\"50\" tabindex=\"5\"<br>";

print "<p align=left><label>State (abbrev.): <input type=\"text\" name=\"STATE\" size=\"2\" maxlength=\"2\" tabindex=\"6\"<br>";

print "<p align=left><label>Zip Code: <input type=\"text\" name=\"ZIP\" size=\"5\" maxlength=\"5\" tabindex=\"7\"<br>";

print "<p align=left><label>Contact Phone Number: (<input type=\"text\" name=\"PHONE1\" size=\"3\" maxlength=\"3\" tabindex=\"8\"";
print "<label>)<input type=\"text\" name=\"PHONE2\" size=\"3\" maxlength=\"3\" tabindex=\"9\"";
print "<label>-<input type=\"text\" name=\"PHONE3\" size=\"4\" maxlength=\"4\" tabindex=\"10\"<br>";

print "<p align=left><label>Email: <input type=\"text\" name=\"EMAIL\" size=\"50\" maxlength=\"50\" tabindex=\"11\"<br>";
print "<p align=left><input type=\"submit\" value=\"Book Now!\"
    name=\"submit\">";

print "<input type=\"reset\" value=\"reset\"
    name=\"reset\"></form>";


// Close the database connection
mysql_close($con);
?>

Can someone help me update the selected RID field with the remainder of the fields from the form above, point me in the right direction?  Thanks.

Im trying to insert some values automatically into a table once the form loads, but Im getting an error. Here is the code

Code: [Select]
<?php
$aid = $_GET['aid'];
$sd = $_GET['sd'];


?>


<style>
#message {margin:20px; padding:20px; display:block; background:#cccccc; color:#cc0000;}
</style>

<div id="message">Your notification has been submitted.</div>

<div style="text-align:center ">
<?php
$connection = mysql_connect("localhost",
        "username",
        "password");

    mysql_select_db("articles", $connection); 

$query="INSERT INTO broken_links (articleid, article) VALUES ('$aid', '$sp')";
if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "Submitted";
mysql_close($con)
?>

<table border="0" cellpadding="3" cellspacing="3" style="margin:0 auto;" >
 
<input type="submit" id="Login" value="&nbsp;&nbsp;Thank you. Please press to close&nbsp;&nbsp;" onclick="tb_remove()"></td>
</tr>
</table>
</div>

Any help will be appreciated

I am trying to place a unique number into a mysql table.

Currently, my code generates a random number, then is supposed to scan through the table for that number.
If the code finds that number already in the table, it generates a new random number and repeats the process.
I have commented my code for the purpose of this help forum:

Code: [Select]
        $result = mysqli_query($link,"SELECT * FROM testTable");

do
{
$end = true; //prepares end of loop
$idNum = rand(1,10); //rand(1,999999);  <-- for testing purposes I have reduced the number generated
$idNumTx = (string)$idNum;

                //loop through the rows
while ($row = mysqli_fetch_assoc($result))
{
if ($row['idNum'] = $idNum) //check if the random number equal to this row
{
$end = false; //prep end of loop repeat
echo $idNumTx; //display rand number that failed for testing purposes
echo " NO! "; //display error for testing purposes
}
}
} while(!$end);

I know I must be doing something wrong, as when I run this, it runs the if statement within the while loop always executes, and I get an output like:
Code: [Select]
1 NO! 1 NO! 1 NO! 1 NO! 1 NO! 1 NO! 5 Win

Win is when it places the value before it, in this case 5, into the table. However, the value of 5 might already be in the table and it doesn't seem to matter.
I execute the code multiple times, and it seems to increase the number of "# NO!" almost (but not every) time. However, each time ALL of the "# NO!" are the same #, and the "# Win" just seems to be random (as it should be, but not unique).

Checking the table after shows me random numbers between 1 and 10 (as it should) in the correct field, but the are not unique. (Ex/ Both row 1 and 5 could have the same value, say 6)

I'm hopefully doing something simple wrong, so someone please point it out to me

I have a script that is supposed to allow a user to add their details to a members table in the database.

It acts as if the user has been added to the database by stating the thank you message, but there is no entry into the database.

I can connect to the database and have another script running OK that allows me to view all of the information in the members table with a loop (this data was entered using php my admin's GUI).

Here is the script that is supposed to add the member.

Any help would be greatly appreciated.

<?php

error_reporting(-1);

$user_name = "root";
$password = "";
$database = "test";
$server = "localhost";

$db_handle = mysql_connect($server, $user_name, $password);

$db_found = mysql_select_db($database, $db_handle);

if ($db_found)
{
$SQL = "INSERT INTO members (fname, sname, email, password, gender) VALUES ('$_POST[fname]','$_POST[sname]','$_POST[email]','$_POST[password]','$_POST[gender]')";
$result = mysql_query($SQL);

mysql_close($db_handle);

print "Thanks for joining us ".$_POST['fname'].".";
}
else
{
print "Database NOT Found";
mysql_close($db_handle);
}

?>

Right, it's hard to explain, but I'll try my best.
Litterally, I have a 'tab' box on my homepage.


And a table called 'news'. Now, the table has the rows "shorttitle" "shortstory" and "image" in it.
I want the title the three latest titles to be on the three tabs, and the story and image in the respective content areas. (The table auto increments the ID, and so order by ID descending limit of 3 yes?)

Now, the code for the tabs is:
Code: [Select]
<?php
$query = "SELECT * FROM news where published = '1' ORDER by id DESC LIMIT 3";
$row_news = mysql_fetch_array($query);
?>
<div id="myTabs">
         <ul>
            <li><a href="#firsttab"></a><? echo $row_news['shorttitle']; ?></li>
            <li><a href="#secondtab">newsID2</a></li>           
            <li id="last"><a href="#thirdtab">newsID3 </a></li>           
        </ul>
<div id="firsttab" class="tab_content">
 <div class="rafat">
 <img src="<? echo $row_news['image']; ?>" />
 <p> <? echo $row_news['shortstory']; ?> </p>
 </div>
</div>
<div id="secondtab" class="tab_content">
 <div class="rafat">
 <img src="newsIMAGE2" />
 <p> newsSTORY2 </p>
 </div>
</div>
<div id="thirdtab" class="tab_content">
 <div class="rafat">
 <img src="newsIMAGE3" />
 <p> newsSTORY3 </p>
 </div>
</div>
</div>
Now, i've filled in the first tab with the MYSQL queries, but i'm unsure how to fill in the other two, because of the way it's coded.. I can't do a 'repeating' code, because of the IDs of the divs, and the three tabs are bunched together..

Anyone any ideas?
Much apreciated.

Thanks
Luke

Greetings,   My current code logs into a database, opens a table named randomproverb, randomly selects 1 proverb phrase, and then SHOULD display the proverb in the footer of my web page. As of right now, the best I can do is get it to display "Array", but not the text proverb... this code below actually causes my whole footer to not even show up. Please help!

<?php

include("inc_connect.php"); //Connects to the database, does work properly, already tested

$Proverb = "randomproverb";
$SQLproverb = "SELECT * FROM $Proverb ORDER BY RAND() LIMIT 1";
$QueryResult = @mysql_query($SQLproverb, $DBConnect);
	while (($Row = mysql_fetch_assoc($QueryResult)) !== FALSE) {
	echo "<p style = 'text-align:center'>" . {$Row[proverb]} . "</p>\n";
	}
	$SQLString = "UPDATE randomproverb SET display_count = display_count + 1 WHERE proverb = $QueryResult[]";
	$QueryResult = @mysql_query($SQLstring, $DBConnect);
...
?>

Code: [Select]
<?php

//Register 1.0.1
include("function.php");
connect();
$num = 0;
if(isset($_POST['submit']))
{

if($_POST['username'] == '' || $_POST['password1'] == '' || $_POST['password2'] == '')
{
$num = 1;
echo "Please supply all fields;";
exit();
}
elseif(strlen($_POST['username']) > 25)
{
$num = 1;
echo "Username must be under 25 characters long;";
exit();
}
elseif(strlen($_POST['password1']) > 16)
{

if(strlen($_POST['password2']) >16)
{
$num = 1;
echo "Password are over 16 characters long;";
exit();
}
$num = 1;
echo "Passwords are not equal";
exit();

}
elseif($_POST['password1'] != $_POST['password2'])
{
$num = 1;
echo "Passwords are not equal;";
exit();
}
}

else 
{
if(isset($_POST['submit']))
{
$username = $_POST['username'];
safe($username);
if($_POST['password1'] = $_POST['password2'])
{
$password = $_POST['password'];
password($password);
echo "INSERT INTO user (username, password) VALUES ('$username','$password')";
}
else {
echo "Passwords are not equal; Double Take";//Yes Double Take is a military term
exit();
}
}
}

?>

<HTML>
<form action="register.php" method="post">
Username:<input type="text" name="username">
<br>
Password:<input type="password" name="password1">
<br>
Password Check:<input type="password" name="password2">
<br>
<input type="submit" name="submit" value="Register!">
</HTML>

Here is where I put the code:

   if(isset($_POST['submit']))
   {
      $username = $_POST['username'];
      safe($username);
      if($_POST['password1'] = $_POST['password2'])
      {
         $password = $_POST['password'];
         password($password);
         echo "INSERT INTO user (username, password) VALUES ('$username','$password')";
      }
      else {
         echo "Passwords are not equal; Double Take";//Yes Double Take is a military term
         exit();
      }

I used it like that to narrow it down. And I still do not understand the issue, are there any other issues?


[attachment deleted by admin]

I have a survey that uses colors. At the moment someone takes the survey I need to capture the values that are in a table called "tbl_colors" and write them to each survey in "tbl_surveys" as a row.

tbl_colors
id, hex_color
1 FFF432
2 CC3399
3 DD3399
and so on to the number 12

tbl_survey
id, timestamp, name, hex01, hex02, hex03, hex04, etc to hex12

I have written code that "works" but I know it is extremely redundant and overloaded and someone could probably do the same thing in less than 10 lines of code. So, I post it here so I can learn from my mistakes.


// Make a MySQL Connection
$query = "SELECT * FROM tbl_colors WHERE id = 1"; 
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result);
$color1 = $row['hex_color'];
$query = "SELECT * FROM tbl_colors WHERE id = 2"; 
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result);
$color2 = $row['hex_color'];
$query = "SELECT * FROM tbl_colors WHERE id = 3"; 
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result);
$color3 = $row['hex_color'];
$query = "SELECT * FROM tbl_colors WHERE id = 4"; 
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result);
$color4 = $row['hex_color'];
$query = "SELECT * FROM tbl_colors WHERE id = 5"; 
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result);
$color5 = $row['hex_color'];
$query = "SELECT * FROM tbl_colors WHERE id = 6"; 
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result);
$color6 = $row['hex_color'];
$query = "SELECT * FROM tbl_colors WHERE id = 7"; 
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result);
$color7 = $row['hex_color'];
$query = "SELECT * FROM tbl_colors WHERE id = 8"; 
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result);
$color8 = $row['hex_color'];
$query = "SELECT * FROM tbl_colors WHERE id = 9"; 
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result);
$color9 = $row['hex_color'];
$query = "SELECT * FROM tbl_colors WHERE id = 10"; 
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result);
$color10 = $row['hex_color'];
$query = "SELECT * FROM tbl_colors WHERE id = 11"; 
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result);
$color11 = $row['hex_color'];
$query = "SELECT * FROM tbl_colors WHERE id = 12"; 
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result);
$color12 = $row['hex_color'];

//echo $color1.$color2.$color3.$color4.$color5.$color6.$color7.$color8.$color9.$color10.$color11.$color12;


mysql_query("INSERT INTO tbl_surveys (hex01, hex02, hex03, hex04, hex05, hex06, hex07, hex08, hex09, hex10, hex11, hex12)
VALUES ('$color1', '$color2', '$color2', '$color3', '$color4', '$color5', '$color6', '$color7', '$color8', '$color9', '$color10', '$color11', '$color12')");

I have a table created containing the following.

name url members date

I want to populate a table like so:

Dates name1 name2 name3
date   membersforname1 membersforname2 membersforname3
anotherdate   membersforname1 membersforname2 membersforname3

So I need the dates to form each of the rows, but also, have the members in the correct places for the names.

Not sure if any of that made sense, if you want me to go in to more detail just ask.

Hey Guys

I have the following table: (login)


I need that table to be echoed he


But it needs to filter it for the school they are logged in as.

Help: When they login they have to select what school they are at so you can use that code.
example: PHS = Poole High School

guys, please help

i need balance amount of fees all students has to pay. Following is the DB structure. `feepayid` is primary key for paymentstable and paymentregister & `studentid` is primary in studentstable. I have written a query for this, please evaluate the query.

paymentstable
feepayid classid paydate paymode feeamount remarks feediscount 32 8 2012-03-06 19:32:35 1 500 hgfhf 0 31 8 2012-03-04 19:32:35 1 800 hgfhf 0 30 8 2012-02-06 19:32:35 1 1200 hgfhf 0 29 8 2012-02-06 19:32:35 1 1100 hgfhf 0
paymentregister
feepayid payid totalfee studentid 32 3 1500 2 31 2 3500 2 30 1 4500 4 29 3 5500 4
studentstable
studentid studentname 2 john 4 mathew 5 peter 6 mary
Code: [Select]
SELECT d.`studentid`, SUM(a.`totalfee` - (SELECT SUM(`feeamount`) FROM `paymentstable`  WHERE a.`feepayid` = `feepayid`)) AS balancefee
FROM `studentstable` d
LEFT JOIN `paymentregister` a ON a.`studentid` = d.`studentid`
GROUP BY d.`studentid`;

Hello,
I am trying to get a value from a MYSQL table, and echo it. It is a shopping cart, then tells you how much you have spent. Most other scripts have done, but I'm having some trouble getting the value back.

Here's the code where I need it inserted:
Code: [Select]
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>RC-Chemicals - <?php echo $title ?></title>
<!--css -->
<link href="./css/style.css" type="text/css" rel="stylesheet" />
<link rel="stylesheet" type="text/css" href="./fancybox/jquery.fancybox-1.3.4.css" media="screen" />
<!--js -->
<script type="text/javascript" src="./js/jquery.js"></script>
<script type="text/javascript" src="./js/chili-1.7.pack.js"></script>
<script type="text/javascript" src="./js/jquery.cycle.all.2.72.js"></script>
<script type="text/javascript" src="./js/cufon-yui.js"></script>
<script type="text/javascript" src="./js/sansation.js"></script>
<script type="text/javascript" src="./js/carusel.js"></script>
<script type="text/javascript" src="./js/script.js"></script>
<script type="text/javascript" src="./js/functions.js"></script>
<script type="text/javascript" src="./js/jquery.sudoSlider.js"></script>
<script type="text/javascript" src="./fancybox/jquery.mousewheel-3.0.4.pack.js"></script>
<script type="text/javascript" src="./fancybox/jquery.fancybox-1.3.4.pack.js"></script>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="shortcut icon" href="images/favicon.ico" type="image/x-icon"/></head>
<body>
<div id="wrapper">
  <div id="header"> <a href="index.php"><img src="./images/logo.png" alt="" id="logo" /></a>
    <div class="right_header">
      <div class="cart-box">
        <div class="cart-left"></div>
<?php
        if (!isset($_SESSION['user'])) {
    echo "<div class=\"cart-content\">
          <ul>
            <li><a href=\"#\">00 item</a></li>
             <li><a href=\"#\"><span>50.00</span></a></li>
          </ul>
        </div>
        <div class=\"cart-right\"></div>
      </div>";
}
else {
 echo "<div class=\"cart-content\">
          <ul>
         
            <li>Please login to purchase.</li>
          </ul>
        </div>
        <div class=\"cart-right\"></div>
      </div>";
}
?>
      <div class="account_login">
        <ul>
         
          <li><a href="login.php">Login </a></li>
        </ul>
      </div>
      <div class="search_field">
        <input type="text" value="Enter keywords to search" onfocus="if(this.value == 'Enter keywords to search') { this.value = ''; }" onblur="if(this.value == '') { this.value = 'Enter keywords to search'; }" />
        <a href="#">Search</a> </div>
    </div>
    <div id="nav">
      <ul>
        <li><a href="#">Methoxetamine</a></li>
        <li><a href="#" class="active">Products</a>
          <ul>
            <li>MDAI</li>
            <li><a href="#">Methoxetamine</a></li>
            <li><a href="#">Methiopropamine</a></li>
            <li><a href="#">NRG-3</a></li>
            <li><a href="#">AMT</a></li>
            <li><a href="#">5-APB</a></li>
            <li><a href="#">Benzo Fury Pellets)</a></li>
          </ul>
        </li>
       
        <li><a href="#">Contact</a></li>
        <li><a href="#">Forums</a></li>
       
         
    </div>
    <br class="clear" />
  </div>
Code: [Select]
<div id="wrapper">
  <div id="header"> <a href="index.php"><img src="./images/logo.png" alt="" id="logo" /></a>
    <div class="right_header">
      <div class="cart-box">
        <div class="cart-left"></div>
<?php
        if (!isset($_SESSION['user'])) {
    echo "<div class=\"cart-content\">
          <ul>
            <li><a href=\"#\">00 item</a></li>
             <li><a href=\"#\"><span>50.00</span></a></li>
          </ul>
        </div>
        <div class=\"cart-right\"></div>
      </div>";
}
else {
 echo "<div class=\"cart-content\">
          <ul>
         
            <li>Please login to purchase.</li>
          </ul>
        </div>
        <div class=\"cart-right\"></div>
      </div>";
}
?>
Extra details:
Table name: tbl_users
Column name: cart
WHE email='$_SESSION['user']'

Thank you guys very much!

Hi,

I don know how to explain this in words too well, so i have create 2 images to show what I need.

What I need to do is use whats in the first image (table.jpg)(the database) to create whats in the second image (dropdown.jpg)(the drop-down menu)

The results of the form will be entered into the database.  category_id will be the value thats inserted.

Many kind regards

Eoin





[attachment deleted by admin]