PHP - Calling Multiple Sql Tables At Once

This is a bit of a more advanced question so I am hoping there are some advanced programmers online at this time . Anyways basically I want to be able to "queue" functions from a class in a single call and am wondering if I am doing it correctly or if there is a better way to do it. Here is my code:


<?php
class test {
     private static $one;
     private static $two;
     private static $three;

     public function callOne($val){
          $one .= $val;
          return $this;
     }
     
     public function callTwo($val){
          $two .= $val;
          return $this;
     }

     public function callThree($val){
           $three .= $val;
           return $this;
     }

     public function print(){
          echo $this->one.' '.$this->two.' '.$this->three;
     }
}
?>


now when I want to call this I can do:

$test = new test();
$test->callOne('one')->callTwo('two')->callThree('three');
$test->callOne('another one');
$test->print();


Is there a better way to do this or is there a different method of doing this? Just wanna make sure I am doing it right lol.

Hi, I want to be able to retrieve records from db tables to build an xml file. The problem I am having is that I don't know how to retrieve a value after using foreign key to get appropriate value. You see below I want to make a new attribute to store to var $newAttr.

Code: [Select]
<?php
$doc=new DOMDocument();
mysql_connect("localhost","root");
mysql_select_db("xmlDB");

$edges=mysql_query("SELECT * FROM edge");
while($row=mysql_fetch_assoc($edges)) {
if($row['flag']=='val') {
$curValue=mysql_query("SELECT val FROM value,edge WHERE val.edge_id=edge.edge_id");
if($curRowIsAttribute=mysql_query("SELECT is_Attribute FROM value,edge WHERE value.is_Attribute='Y' AND value.edge_id=edge.edge_id")) {
//create attribute node
$newAttr=$dom->createAttribute();//HELP HERE PLEASE THEN I SHOULD BE ABLE TO COMPLETE NEXT TWO LINES BELOW!

//set attribute value
...

//append attribute to current element
...

}//END IF
}//END IF
}//END BIG WHILE 
?>

Any help much appreciated!

I am working on a asset project to insert, delete and view assets (items) that i owe. I am having trouble in the insert part.

i have four tables

department (DeptID, DeptName) DeptID is the primary key in this table.

assetcategory (AssetCatID, AssestCategory) AssetCatID is the primary key in this table.

asset (AssetID, AssetDescription, EmpID, AssetCatID, DeptID, Model, Maker, SerialNUm, DateAguired) AssetID is the primary key here.

Employee (EmpID, FirstName, LastName) EmpID is the key here.

and this is the code i have to insert using php.
Code: [Select]
<?php 
if(isset($_POST['submit'])){

$FirstName = mysql_real_escape_string($_POST["FirstName"]);
$LastName = mysql_real_escape_string($_POST["LastName"]);
$DeptName = mysql_real_escape_string($_POST["DeptName"]);
$AssetCategory = mysql_real_escape_string($_POST["AssetCategory"]);
$Model = mysql_real_escape_string($_POST["Model"]);
$Maker = mysql_real_escape_string($_POST["Maker"]);
$SerialNum = mysql_real_escape_string($_POST["SerialNum"]);
$DateAguired = mysql_real_escape_string($_POST["DateAguired"]);
$AssetDescription = mysql_real_escape_string($_POST["AssetDescription"]);
$AssetCatID = mysql_real_escape_string($_POST["AssetCatID"]);
$AssetID = mysql_real_escape_string($_POST["AssetID"]);
$EmpID = mysql_real_escape_string($_POST["EmpID"]);
$DeptID = mysql_real_escape_string($_POST["DeptID"]);

if(empty($FirstName) || empty($LastName) || empty($DeptName) || empty($AssetCategory) || empty($Model) || empty($Maker) || empty($SerialNum) || empty($DateAguired) || empty($AssetDescription))
{
print "Please feel in all fields";
}
else
{
///insert into the department table..........
$query1= "INSERT INTO department VALUES (null,'{$DeptName}')";
$result1 = mysql_query($query1) or die(mysql_error());
$DeptID = mysql_insert_id();

//insert into the assetcategory table
$query2= "INSERT INTO assetcategory VALUES (null,'{$AssetCategory}')";
$result2 = mysql_query($query2) or die(mysql_error());
$AssetCatID = mysql_insert_id();

//insert into the assets table
$query3= "INSERT INTO assets VALUES (null,'{$AssetDescription}', '{$EmpID}','{$AssetCatID}','{$DeptID}','{$Model}','{$Maker}','{$SerialNum}','{$DateAguired}')";
$result3 = mysql_query($query3) or die(mysql_error());
$AssetCatID = mysql_insert_id();


print $query1;

}



}
else {
?>


the page is displaying this error: "Column 'AssetCatID' cannot be null"

I would need help in inserting into all table required data. any help? hope i am making sense

I'm having a hard time grasping how to select data from several tables.

Here's the code

Code: [Select]
<?php 
require_once "config.php";
$gamedate = $_SESSION['date'];
echo "These umpires are not currently scheduled on this date, and have not asked for the day off:<br />";

$umpirelist = Array('umpire 1 name', 'umpire 2 name', 'umpire 3 name');

$dbc = mysql_pconnect($host, $username, $password);  
mysql_select_db($db,$dbc);  

foreach($umpirelist as $data) { 

//now get stuff from a table           
$sql = "SELECT calendar.date, calendar.ump1, calendar.ump2, calendar.ump3, calendar.ump4, calendar.ump5, vacation.date, vacation.umpire FROM umps, calendar, vacation WHERE umps.full_name = $data AND $gamedate = `calendar.date` AND $gamedate = `vacation.date` AND $data NOT IN ('calendar.ump1', 'calendar.ump2', 'calendar.ump3', 'calendar.ump4', 'calendar.ump5') AND $data NOT IN ('vacation.umpire') order by umps.full_name asc"; 

$rs = mysql_query($sql,$dbc);  
$matches = 0; 
while ($row = mysql_fetch_assoc($rs))  {
$matches++; 
echo "$row[$data]<br />";
}
}
?>

The table CALENDAR holds the date of the game, who is playing, and what umpires are schedule for that game.  it does have a unique "row_number" column as well, but didn't think I'd need it in this select.

The table VACATION has the date, and umpire's name that is on vacation.

The table UMPS contains the umpires name (same names as the array in the code) and email addresses and login info.

I'm getting this error:

Quote

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource on line 152



This is line 152

Quote

while ($row = mysql_fetch_assoc($rs))  {



Can someone please point me in the right direction as to where I'm going wrong?

Thanks!

How do I search through table 1, 2, 3 where a=x and b=y and c=z?

$query = mysql_query("SELECT COUNT(tickets.id),tickets.id,tickets.status,tickets.date,tickets.question,tickets.title,users.position FROM tickets,users WHERE tickets.id='$existing' users.username='$username'");


How do I get it so it selects data from the table tickets where id='$existing', but I also want to get a user's (who is viewing the ID) position (rank). $username is a session. So, any thoughts on how I would do so?

My updated code:


<?php
session_start();
include("../includes/mysql.php");
include("../includes/config.php");

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<link href="../style.css" rel="stylesheet" type="text/css" />
<title><?php echo $title; ?></title>
</head>

<body>
<div id="container">
<div id="content">
<div id="left">
<div class="menu">
<?php include("../includes/navigation.php"); ?>
<div class="menufooter"></div>
</div>
<?php include("../includes/menu.php"); ?>
</div>
<div id="middle">
<?php
$existing = $_POST['existing'];
$ip = $_SERVER['REMOTE_ADDR'];
$username = $_SESSION['user'];

$query = mysql_query("SELECT COUNT(tickets.id),tickets.id,tickets.status,tickets.date,tickets.question,tickets.title,users.position FROM tickets,users WHERE tickets.id='$existing' OR users.username='$username'");
$get = mysql_fetch_assoc($query);

if(!$existing)
{
echo 
'
<div class="post">
<div class="postheader"><h1>Error</h1></div>
<div class="postcontent">
<p>You have not enetered in a ticket ID. Please go back and do so.</p>
</div>
<div class="postfooter"></div>
</div>
';
}
elseif($get['COUNT(id)'] < 1)
{
echo 
'
<div class="post">
<div class="postheader"><h1>Error</h1></div>
<div class="postcontent">
<p>The ticket ID you are trying to use doesnt exist. Please go back or submit another ticket.</p>
</div>
<div class="postfooter"></div>
</div>
';
}
elseif($get['tickets.ip']==$ip || $get['user.position'] >= 1)
{
$status["tickets.status"]["0"] = "Waiting for support...";
$status["tickets.status"]["1"] = "Waiting for user...";
$status["tickets.status"]["2"] = "Ticket Closed...";
$status["tickets.status"]["3"] = "Ticket Opened...";

echo 
'
<div class="post">
<div class="postheader"><h1>View Ticket Status - ID '. $get["id"] .'</h1></div>
<div class="postcontent">
<p>Title: '. $get["tickets.title"] .' - Posted on: '. $get["tickets.date"] .'</p>
<p>Ticket Status: '. $status[$get["tickets.status"]] .'</p>
<p>Question: '. nl2br($get["tickets.question"]) .'</p>
</div>
<div class="postfooter"></div>
</div>
';

}
else
{
echo 
'
<div class="post">
<div class="postheader"><h1>Error</h1></div>
<div class="postcontent">
<p>This is not your ticket. You only have permission to view your tickets. Please go back.</p>
</div>
<div class="postfooter"></div>
</div>
';
}
?>
</div>
</div>
</div>
</body>
</html>

Need help in updating the tables

I have array of data coming from a form which I'm inserting in my DB. I have 5 tables
product
filter
product_filter
heater
product_heater

Im able to put the data in the "product", "filter" & "heater" tables but i dont know how to put data inside the "product_filter" & "product_heater" table. Any help or direction to any tutorails is appreciated.

My Tables structu

product
id int(5)
product text
cost text
details text

filter
id int(5)
filter text
imgpath text

product_filter
id int(5)
id_product int(5)
id_filter int(5)

heater
id int(5)
heater text
imgpath text

product_heater
id int(5)
id_product int(5)
id_heater int(5)


Code: [Select]
// Product data Update
$name = mysql_real_escape_string($_POST['product']);
$cost = mysql_real_escape_string($_POST['cost']);
$details = mysql_real_escape_string($_POST['details']);

$sql_title = "INSERT INTO product (
id ,
product ,
cost ,
details ,
)
VALUES (
NULL , '$name' , '$cost' , '$details')";
if (!mysql_query($sql_title,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "records for product added<br />";


// Filter update
// This is the array which is coming from the form
/*
Array ( [0] => ehiem
[1] => Hagan
[2] => Rena
[3] => jobo )


Array ( [0] => img1.jpg
[1] => img2.jpg
[2] => img3.jpg
[3] => img4.jpg )
*/

$filtername = mysql_real_escape_string($filtername);
$filterimgpath = mysql_real_escape_string($filterimg);
$combined_array = array_combine($filtername, $filterimgpath);
$values = array();
foreach ($combined_array as $filtername => $filterimgpath)
{
    $values[] = "('$filtername', '$filterimgpath')";
}
$sql = "INSERT INTO filter (filter , imgpath) VALUES " . implode(', ', $values);
//echo $lastid = mysql_insert_id()."<br />";
if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "records added<br />";

//Product Filter Update table
// This is where Im stuck. Not able to even think of anything....


// heater update
// This is the array which is coming from the form
/*
Array ( [0] => ehiem
[1] => Dolphin
[2] => Rena
[3] => jobo )


Array ( [0] => img1.jpg
[1] => img2.jpg
[2] => img3.jpg
[3] => img4.jpg )
*/

$heatername = mysql_real_escape_string($heatername);
$heaterimgpath = mysql_real_escape_string($heaterimg);
$combined_array = array_combine($heatername, $heaterimgpath);
$values = array();
foreach ($combined_array as $heatername => $heaterimgpath)
{
    $values[] = "('$heatername', '$heaterimgpath')";
}
$sql = "INSERT INTO heater (heater , imgpath) VALUES " . implode(', ', $values);
//echo $lastid = mysql_insert_id()."<br />";
if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "records added<br />";

//Product heater Update table
// This is where Im stuck. Not able to even think of anything....

Hi All,

I am adding a button that will delete many things in the system which all have the same $job_id

There is going to be 10 or so tables that need to delete *  where $job_id = ?

Is there a better way to do this rather than just iterating my code 10 times.

I was looking at just joining but isnt one benefit of prepared statements that they can be used again and again to increase speed.

Hi,

I'm very new to PHP and have managed to get through most of the problems I've encountered so far, but I have got to one point that I can't seem to solve.

I have two mysql tables (Categories & Products), I have a page that shows the categories (generated from the categories table) and when a category is selected it lists the products in that category.

Due to stock etc. not all categories always have products associated to them.

The Category & Product Tables are automatically updated, so what I need to do is only show categories that have products associated to them, both tables have a column category_no, and nothing I've tried so far seems to work.

The code I have so far is:-

Code: [Select]
Require_once('config.php');

Mysql_connect(db_host, db_user, db_password);

@mysql_select_db(db_database) or die( "unable to select database");

$query="select * from categories";

$result=mysql_query($query);

$num=mysql_numrows($result);

Mysql_close();
Thanks

Pete

 
I'm not sure if this belongs in the PHP or SQL sections or both but-
Is it possible to insert form data (one record) into 2  different tables when I click submit?
  As is, I have 2 distinct log-in scripts each linked to 2 separate databases .  I want to merge them into one log-in form that populates both tables(in different dbs). I don't want my visitors to log into 2 different forms.  Can one form's action attribute send the POST data to two different processing scripts? and can the input from a text  box be sent to 2 different fields?
I don't have any code, I just wanted to know if it can be done or point me to a sample that I can work from.

Hey,

I have a query running inner joins... which currently works, but I need some more information pulled from other tables...

Code: [Select]
define('WPSC_TABLE_PRODUCT_LIST', "{$wp_table_prefix}wpsc_product_list");
define('WPSC_TABLE_PRODUCTMETA', "{$wp_table_prefix}wpsc_productmeta");
define('WPSC_TABLE_CATREF', "{$wp_table_prefix}wpsc_item_category_assoc");
define('WPSC_TABLE_CATNAME', "{$wp_table_prefix}wpsc_product_categories");


$cf="Linked Products";
$sku = get_post_meta($post->ID, $cf, true);
$array = explode(",",$sku);

$products = array_count_values($array);

foreach($products as $key => $value) {
$product = $wpdb->get_row("SELECT meta.product_id AS pid, list.name AS name, list.price AS price, retailer.category_id AS catid FROM ".WPSC_TABLE_PRODUCTMETA." AS meta INNER JOIN ".WPSC_TABLE_PRODUCT_LIST." AS list ON ( list.id = meta.product_id ) INNER JOIN ".WPSC_TABLE_SHOPNAME." AS retailer ON ( retailer.product_id = meta.product_id ) WHERE `meta_key` IN ( 'sku' ) AND `meta_value` IN ( '{$key}' ) ORDER BY list.id DESC");
?>
But I also need the category name - So I have to cross reference the following...

wordpdem_wpsc_product_list

id      name
433   itemone
432   itemtwo
431   itemthree

wordpdem_wpsc_item_category_assoc

id   product_id    category_id
1    433                    1
2    432                    2
3    410                    3

wordpdem_wpsc_product_categories

id  name
1   Bread
2   Fish
3   Snacks

I've now written:
Code: [Select]
SELECT
meta.product_id AS pid,
list.name AS name,
list.price AS price,
retailer.category_id AS catid,
category.name AS catname

FROM wordpdem_wpsc_productmeta AS meta
INNER JOIN wordpdem_wpsc_product_list AS list ON ( list.id = meta.product_id )
INNER JOIN wordpdem_wpsc_item_category_assoc AS retailer ON ( retailer.product_id = meta.product_id )
INNER JOIN `wordpdem_wpsc_product_categories``wordpdem_wpsc_product_list` AS category ON ( retailer.category_id = category.id )
WHERE `meta_key` IN ( 'sku' ) AND `meta_value` IN ( '{$key}' ) ORDER BY list.id DESC
However it fails on line 12 (the Where statement...)

Any ideas what I've done wrong?

hi im trying to echo out all the possibilities from one column in 2 different tables, both with the same structure. if that makes sense

this is what i have

$query = mysql_query("SELECT * FROM stanjamesprem,centrebetprem GROUP BY eventname ORDER BY eventtime ");
while($row = mysql_fetch_assoc($query))
   {
   echo $row[eventname];
   }

if i take one of the tables out it works fine but if i have 2 table names it doenst work

please help me

cheers matt

any questions please ask away

I have 5 tables that I changed on my development server (laptop) that I'd like to upload to my test server. I can export just those tables from PhpMyAdmin but on import it drops all of the other tables first. Short of editing the SQL before I upload it (finding the drop and deleting it) is there an easy way to uncheck a box and change that behavior that I am just not seeing?

I want to select the user from super_administrators, administrators, teachers and students, and give the user permission based from what table he "came". But is giving the "Query failed" error...

Code: [Select]
<?php

//Start session

session_start();



//Include database connection details

require_once('../config/config.php');



//Array to store validation errors

$errmsg_arr = array();



//Validation error flag

$errflag = false;



//Connect to mysql server

$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);

if(!$link) {

die('Failed to connect to server: ' . mysql_error());

}



//Select database

$db = mysql_select_db(DB_DATABASE);

if(!$db) {

die("Unable to select database");

}



//Function to sanitize values received from the form. Prevents SQL injection

function clean($str) {



$str = @trim($str);



if(get_magic_quotes_gpc()) {

$str = stripslashes($str);

}



return mysql_real_escape_string($str);

}



//Sanitize the POST values

$email = clean($_POST['email']);

$password = clean($_POST['password']);



//Input Validations

if($email == '') {

$errmsg_arr[] = 'O campo Email nao foi preenchido.';

$errflag = true;

}



if($password == '') {

$errmsg_arr[] = 'O campo Senha nao foi preenchido.';

$errflag = true;

}



//If there are input validations, redirect back to the login form

if($errflag) {

$_SESSION['ERRMSG_ARR'] = $errmsg_arr;

session_write_close();

header("location: ../index.php");

exit();

}



//Create query

$qry = "SELECT * FROM super_administrators,administrators,teachers,students WHERE email = '$email' AND passwd = '".md5($_POST['password'])."'";

$result = mysql_query($qry);

$member = mysql_fetch_assoc($result);

$table = mysql_field_table('$result', '0');



//Check whether the query was successful or not



if($result) {



if(mysql_num_rows($result) == 1) {



if($table == 'super_administrators')  {

session_regenerate_id();

$_SESSION['SESS_ID'] = $member['id'];

$_SESSION['SESS_NAME'] = $member['name'];

$_SESSION['SESS_EMAIL'] = $member['email'];

session_write_close();

header("location: ../users/sadmin/index.php");

exit();

}



if($table == 'administrators')  {

session_regenerate_id();

$_SESSION['SESS_ID'] = $member['id'];

$_SESSION['SESS_SCHOOL_ID'] = $member['school_id'];

$_SESSION['SESS_NAME'] = $member['name'];

$_SESSION['SESS_EMAIL'] = $member['email'];

session_write_close();

header("location: ../users/admin/index.php");

exit();

}



if($table == 'teachers')  {

session_regenerate_id();

$_SESSION['SESS_ID'] = $member['id'];

$_SESSION['SESS_SCHOOL_ID'] = $member['school_id'];

$_SESSION['SESS_NAME'] = $member['name'];

$_SESSION['SESS_EMAIL'] = $member['email'];

session_write_close();

header("location: ../users/prof/index.php");

exit();

}



if($table == 'students')  {

session_regenerate_id();

$_SESSION['SESS_ID'] = $member['id'];

$_SESSION['SESS_SCHOOL_ID'] = $member['school_id'];

$_SESSION['SESS_CLASS_ID'] = $member['class_id'];

$_SESSION['SESS_NAME'] = $member['name'];

$_SESSION['SESS_REGISTRATION'] = $member['registration'];

$_SESSION['SESS_EMAIL'] = $member['email'];

session_write_close();

header("location: ../users/aluno/index.php");

exit();

}



} else {



$errmsg_arr[] = 'Suas informacoes de login estao incorreta. Por favor, tente novamente.';

$errflag = true;

$_SESSION['ERRMSG_ARR'] = $errmsg_arr;

session_write_close();

header("location: ../index.php");

exit();

}



} else {

die("Query failed");

}

?>