PHP - Relational Algebra Expression For Given Queries In Simple Database

Hello,

I've created a function whereby I want to return the school_id associated with a particular user.  Each user will be associated with exactly one school.  My query below works, but the thing that it returns is an array; I need to make use of it as a variable (say $instructor_school_id).  Any idea how to make the conversion from a single element array into a non-array variable?

Thank you....

function getInstructorSchool($read, $user_id) {

$sql = "SELECT school_id

FROM users

WHERE user_id = $user_id";

return $read->fetchRow($sql);
}
   

I have been reading in Larry Ullmans book "Visual QuickPro Guide PHP 6 and MySQL 5" and I find it well-written. Since it is from 2008, it does not contain anything about MySQL PDO, but rather does it in the mysqli_* way. Larry suggest placing the secret database password and more along with a database connection script in a connect.php file, placed above the webroot if possible. Then later, when he is creating queries and executing them in other php files, he includes the connect.php file before making the queries.   Now I know it is very important to be careful with the Error handling, so the script won't output errors, which could reveal something about the database making it less secure. Therefore I am wondering how to structure things when using PDO. I need to write error-handling scripts for the following situations:   a) Connection to the database doesn't succeed b) The execution of the queries doesn't succeed c) User input in HTML forms are not appropriate   and probably more. The recommended way of handling errors when using PDO seems to be writing some try-catch code. But then I don't see how I can keep the connection to the database completely inside the connect.php file. Either I will need to use a die() or exit() inside this file or I will need to give up my idea to keep everything which concerns the connection to the database in the file mentioned AND write nested try-catch sentences - first make sure the connection works, then make sure the query will execute properly.   I don't like either of those approaches. Firstly I have been told that using exit() is bad programming and secondly it seems to get more complicated using nested try-catch code and to let database connection take part in diverse php-files.   Maybe somebody have a smart, convenient and secure way to do it?   Erik  

As we know the total number of hits to database is proportional to the number of page views if page views * queries per page is more than the capacity of your database, then mysql queries may show  problem.  is there any way to calculate the database capacity ? I googled someone says 128Mb cache is there for mysql.

Hey guys, i'm new to this site and would need some help with coding. 

So i'm making a car part website which should has brand/model search. It's a dropdown search which will get the brand / model from database and should display all the parts for that exact model, from folder. 

Database structure which i have is id, master, name. ( Here's some pictures to clear out what i'm doing. http://imgur.com/a/7XwVd )  So the problem is that it does not get the images from folder named ex: *_audi_a3.jpg. 

And link to codes what have been written already.     Parts.php: http://pastebin.com/q6vdypge  Update.php: http://pastebin.com/DymhGQ17 Search_images.php: http://pastebin.com/LF5Q0i8f Core.js: http://pastebin.com/bgc0y4TS   I don't know what's wrong with it sadly. One thing i noticed when i used firebug it gives error on category when searching error:true.

Hope you guys understand what i mean here, and also all help is appreciated.

And move this post if it's in wrong place. 

Thanks!
Edited by aeonius, 17 October 2014 - 12:15 PM.

Hi im new to php and I need help making webpage that queries a mysql database based on a 3 check boxes and displays results on the same page or on another page. The table being queried has 4 columns, name, gps, wifi, bluetooth. So for example a row in the table would be like, samsung galaxy s2, yes, yes, yes. The idea is for it to be a website that will display phones according to their features.

So the idea is depending on if the boxes were ticked the samsung galaxy would be displayed as a result. So i need some help understanding how to make this.

Some1 gave me the code below in attempt to help me (im not sure it works or not) but im not sure how fully use it, ie what pages i need to make and how i create the connection to the mysql database, and how to use the query that they wrote to display the results

thanks

code:
Code: [Select]
<form action="?do=filter" method="post">
<table cellspacing="0" cellpadding="3" border="1">
<tr>
<td>GPS<input type="checkbox" name="gps" value="checked"></td>
<td>Wifi<input type="checkbox" name="wifi" value="checked"></td>
<td>Bluetooth<input type="checkbox" name="bluetooth" value="checked"></td>
</tr>
<tr><td><input type='submit' name='filter' value='Filter'></td></tr>
</table>
</form>
</html>
<?php
function filterMe($filter){
if(isset($_POST[$filter])){
return "Yes";
}else{
return "No";
}
}

if(isset($_POST['filter'])){
echo "Gps - " . filterMe('gps');
echo "
Wifi - " . filterMe('wifi');
echo "
Bluetooth - " . filterMe('bluetooth');
}
?>


All you need to do is use a query something like
SELECT name,gps,wifi,bluetooth FROM `product` WHERE `gps`='".filterMe('gps')."' AND `wifi`='".filterMe('wifi')."' AND `bluetooth`='".filterMe('bluetooth')."'

Hi Guys

I have two table in mysql one is users and the second one is profiles

my database name is movies

users contains: users_id, username, password, email

movies contains: id, user_id, name, movie_name, movielink


I need users to login and then update their profile according to relational database setup

so once user loggs in they can add the link of their movies to the database.


Could some one help me with inserting data into relational database according to database setup I have introduced above?

Appreciate your help in advance

I have a form where I have inserted 7 pre-populated relational lists.  All of the information is pulling correctly from the databases, but when it posts, it's posting the value "ids" instead of the chosen text. 

The files a   www.kcwell.com/gcc_form.php and www.kcwell.com/gccsuccess_form.php

How do I set up a query to obtain the data that I need?  Help!

Hi guys,

I want to create a simple database and use PHP to output the database to certain positions on a HTML page.

I am basically trying to recreate this: http://bit.ly/dX4byI

I want to have a form where the event info is selected from drop down box such as: Venue and Date then the the rest of the form data (event name, artists, opening times) will go into the right box depending on this.

Does anyone have any pointers? Am I even going about this the right way?!

i have enters some data using form which submit data in sql table1 like this,

Fields name in table -->>         id   First name     Lastname    Date   
data saved           -->>              1  user                1               2011-05-10
                                               2  user                2               2011-05-11
                                               3  user                3               2011-05-12

now i dont want to duplicate date in database let say while inserting data using form,

Firstname :  ____________
Lasename :  ____________
Date         :  ____________ (i dont want this date to b save if the date alredy exist in database,
                                          it should prompt user "Date already exist and to edit click here
                                         (this will be the link to that date which is already exist so that we can edit) " )

                                SAVE


i user  files ,       form.php ,   insert.php(so insert values in database) ,
so tell me what function should i use to solve my problem....
 

I have 2 tables and am trying to conduct an SQL query. I simply need to know how to use a JOIN when two fields in each table are needed to match the records up. I would rather not create another field in each table.

I have put my query below which would work other than for what is obviously not possible ("ON xyz=xyz && abc=abc" etc.)

Table 1 "Employees"

employeeDept / deptEmployeeRef / name / wage

A / 2 / Dave Smith / 2000
B / 2 / Robert Brown / 2500


Table 2 "Deductions"

monthNo / employeeDept / deptEmployeeRef / deduction

32 / B / 2 / 300
32 / C / 2 / 300
32 / A / 3 / 300
33 / B / 2 / 500


Each department has employees starting from number 1 so two fields are needed in each table to match the records up.

SELECT t1.name, (t1.wage - t2.deduction) FROM $tableName1 AS t1 LEFT JOIN $tableName2 AS t2 ON t1.employeeDept=t2.employeeDept && t1.deptEmployeeRef=t2.deptEmployeeRef WHERE t2.monthNo = '32'

So this should return:
Dave Smith 2000
Robert Brown 2200

First time poster, and very amateur php coder.  I am trying to delete items that in a list using ajax.  I can't figure out what I am doing wrong.  Any help would be greatly appreciated!

 

Here is a snippet of my javascript...

    $('.delete-item').click(function() {
        var itemID = $(this).data('itemID');
        var clear = 1;
        $.ajax({
            url: 'includes/delete-item.php',
            method: 'POST',
            data: {
                itemid:itemID,
                clear:1
            },
            success: function(data) {
                $('.content').load('includes/lists.php')
            }
        })
    })

 

and here is the relevant php...

$item = $_POST['itemid'];
$clear = $_POST['clear'];
$clearSQL = "DELETE FROM `List_Items` WHERE `item_ID` = $item";
$cleared = mysqli_query($connect, $clearSQL);

I cant figure out why im having this problem.
i stuck this at the top of my page on its own and it wont update the row to read

Code: [Select]
<?php
mysql_query("UPDATE pm SET read= 'read' WHERE pm_id = '".mysql_real_escape_string($_GET['pm_id'])."'");
?>
I echo $_GET['pm_id']; and that turn out correct and the number matches the pm_id on the row.
everything is lowercase
I typed read manually into the column and echo that and it showed read
also I have this same exact line of code on another script that works fine.

does someone see something im over looking? this is driving me crazy


http://localhost/stargate/users/view.php?pm_id=1376672&inbox=2

Code: [Select]
<?php require("menu.php"); ?>

<html>
<body>
 

<?php
mysql_query("UPDATE pm SET read= 'read' WHERE pm_id = '".mysql_real_escape_string($_GET['pm_id'])."'");

?>



</body>
</html>

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=314391.0

Hey,

I'm trying to work out how I can have a simple script that will work like this:

Page 1 - Heading, Paragraph, Image read from database.
Page 2 - Has text boxes allowing you to change heading, paragraph and image (url) with each edit using a different text box. (Admin panel)

The database connection would be in a separate file.

So far I've got the database connection sorted.

Code: [Select]
<?php
//Database Information
$dbtype = "mysql"; 
$db_host = "localhost";
$db_user = "";
$db_pass = "";
$db_name = "";
$db_port = "";
$db_table_prefix = "userCake_";
?>
At the top of each of the other pages this is what I've got.
Code: [Select]
<?php 
require_once("models/config.php");
?>
Let's say I give each bit stored in the database a page ID and an object ID.

If the page selected is index.php how can we give it a page ID of 1 and give then include all the separate 'objects' throughout the page?

Hopefully this makes sense?

Cheers,
Jack

I am trying to do something that a hell of a lot of people must need to do every day, yet despite this I can not find a single example online, or in books, which makes absolutely no sense!

Anyway, hopefully it will be blindingly obvious to someone what I'm doing wrong, I really hope so as I can't figure it out...

I have a mysl table as follows:

tblvenue

venue_id PRIMARY KEY (int)
venue_name (varchar 50)
station_id (int)

I have a php/html page as follows:

<html>
<head>
<title>Update & Delete Venues</title>
</head>
<body marginheight="0" topmargin="0" marginwidth="0"
leftmargin="0" style="margin:0;padding:0" bgcolor="#B0E0E6">
<h1></h1>

<form action="venue4.php" method="post">
<table border="1" style="border:solid black" cellpadding="0"
cellspacing="0" width="100%">
<tr>
<td align="center" width="33%">Action</td>
<td align="center" width="33%">Venue Name</td>
<td align="center" width="33%">Station Name</td>
</tr>
<?php

$station_name=$_POST['station_name'];

@ $db = new mysqli('XXX', 'XXX', 'XXX', 'XXX');
$query = "call sp_venue()";
$result = $db->query($query);
$num_results = $result->num_rows;

function db_result_to_array($result){
//---------------------------------------------------------------------
$res_array = array();

for ($count=0; $row = $result->fetch_assoc(); $count++) {
$res_array[$count]=$row;
}

return $res_array;
}
//---------------------------------------------------------------------



function get_dataset($server, $user, $password, $database, $query7) {
//---------------------------------------------------------------------
$db1 = new mysqli($server, $user, $password, $database);
$query1 = $query7;
$result1 = $db1->query($query1);
$num_results = $result1->num_rows;
$result8 = db_result_to_array($result1);
return $result8;}
//---------------------------------------------------------------------


   echo "<tr>";
   echo "<td>";
   echo "<table width='100%'><td width='33%' align='center'><input type='submit' id='btnupdate' name='btnupdate' value='Update & Delete' ></td>";
   echo "<td width='33%' align='center'><input type='submit' name='btncancel'id='btncancel' value='Cancel Updates' ></td>";
   echo "<td width='33%' align='center'><input type='submit' value='Add New Venue' name='btnadd'></td></table>";
   echo "</td>";
   echo "<td><input type='text' name='tbvenue_name'/></td>";
   echo "<td>";
?>
<select name="stat_id">
<?php
$stat_array=get_dataset('XXX', 'XXX', 'XXX', 'XXX',"call sp_station");
foreach ($stat_array as $thisstat){
echo "<option value=\"".$thisstat['station_id']."\"";
echo ">".$thisstat['station_name']."</option>";
}
?>
</select>
<?php
   echo "</td>";
   echo "</tr>";
   echo "<tr>";
while($row = $result->fetch_assoc())
   {
   echo "<td width='33%'>Delete:<input type='Checkbox' id='cbdelete[]'  name='cbdelete[]' value='".$row['venue_id'];
   echo "'>Update:<input type='Checkbox' id='value'  name='cbupdate[]' value='".$row['venue_id']."'><input type='hidden' name='venue_id[]' value='".$row['venue_id']."'></td>";
   echo "<td width='33%'><input type='Textbox' width='100%' name='venue_names' value='" .$row['venue_name']. "'></td>";
   echo "<td width='33%'>"  ;
   echo "<select name='station_id[]' width='100%'>";
$stat_array=get_dataset('localhost', 'root', 'snooker1', 'pool',"call sp_station");

foreach ($stat_array as $thiscat)
{
    echo '<option value="' . $thiscat['station_id'] . '"';
    if ($row['station_id'] == $thiscat['station_id']) {echo ' selected';}
    echo '>' . $thiscat['station_name'] . '</option>';
}



   echo "</td>";
   echo "</tr>";
   }
 echo "</table>";

?>


</form>
</body>
</html>

This displays correctly and when checking the appropriate checkbox and clicking the script venue4.php is run:

<?php

//Connects to the database
$db = new mysqli('XXX', 'XXX', 'XXX', 'XXX');

//Checks to see if the update button has been clicked
if (isset($_POST['btnupdate']))
{

//Assigns checkbox array to variable
$aDel = $_POST['cbdelete'];

$aVal = $_POST['cbupdate'];


//Loops through rows to delete selected records
foreach($_POST['cbdelete'] as $delete11)

{

//Deletes record
$query = "delete from tblvenue where venue_id =".$delete11;
$result = $db->query($query);
$num_results = $result->num_rows;
 
}



//Loops through rows to delete selected records
foreach($_POST['cbupdate'] as $update11)

{
//Gets variable


//$Venue = $_POST['venue_names'];

//Updates record
$query = "update tblvenue set venue_name=".$venue." where venue_id =".$update11;
$result = $db->query($query);
$num_results = $result->num_rows;
 
}

//Checks to see if Add button was clicked
} elseif (isset($_POST['btnadd'])) {

//Looks for Venue Name & Station Name
$tbvenue_name=$_POST['tbvenue_name'];
$tbstat_id=$_POST['stat_id'];

//Formats variables
if(!get_magic_quotes_gpc()) {
$tbvenue_name = addslashes($tbvenue_name);
$tbstat_id = addslashes($tbstat_id);
}

//Inserts new record
$query = "call sp_insert_venue('".$tbvenue_name."',".$tbstat_id.")";
$result = $db->query($query);

} elseif (isset($_POST['btnedit'])) {

switch($page) {
default:
include("venue2.php");
 break;
 }

} else {
//If cancel button is clicked, goes straight to switch code below

}

//Closes database connection
$db->close();

//Goes back to Venue page
switch($page) {
default:
include("venue.php");
 break;
 }

echo var_dump($aval);
print_r($aVal);

//$test = $_POST['cbupdate'];


?>

Deleting records works fine.
Inserting records works fine.
If I hard code the data (ie. set venue_name = 'blah blah blah')  for updates, I can get the relevant row id from cbupdate and update the row in question. However I can not get the data from venue2.php (typed into a textbox on the row in question) and submit this to the database to be updated.

This scenario must come up for zillions of developers, but so far no-one has been able to help.

Please can anyone assist...