Overclock.net › Forums › Software, Programming and Coding › Coding and Programming › HTML PHP and MySQL help !
New Posts  All Forums:Forum Nav:

HTML PHP and MySQL help !

post #1 of 11
Thread Starter 
im bursting my brain here , this is my problem :

i have a simple database ( 4 tables ) each table have foreing key called MEMBER_NUM

i need to find info from THAT member_num in 2 specifi tables , (the other ones share data , but the same technique is applied if i figure out this problem)

Example : table one got the personal info of this member , table 2 got the work order number and a description of that work order , i want to display this info in a single Output and in HTML



i already got a "search" for the customer in HTML with a "get" form , the output of this search is in PHP ... here is my first problem , how can i display this output , in HTML ? i know i can format the php query result and all , but it still a php page . any turnarround , loophole , trick would do it here biggrin.gif

and the other problem i have is , how can i do more than one query in a PHP ? or some kind of loophole that lets me search this MEMBER_NUM in different tables


ill post some code if its needed , ( i have to edit some data just to be safe )
Edited by yoi - 4/30/13 at 12:29am
My Trusty PC
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 1366 950 EVGA Classified 3 EVGA 580 gtx G.skill Pi  
OSKeyboardPowerCase
W7 ultimate 64bit Logitec G510 Corsair  Haf X 
Mouse
Deathadder 
  hide details  
Reply
My Trusty PC
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 1366 950 EVGA Classified 3 EVGA 580 gtx G.skill Pi  
OSKeyboardPowerCase
W7 ultimate 64bit Logitec G510 Corsair  Haf X 
Mouse
Deathadder 
  hide details  
Reply
post #2 of 11
Probably something like this (it would help to have more information about your DB schema though):
Code:
SELECT * FROM customer, workorder
WHERE
    customer.member_num = workorder.member_num AND
    customer.name = user_input

Replace table/column names with whatever they really are.

This will return all columns from all tables in the FROM clause for every record that matches the filter in the WHERE clause. Of course you'll want to replace * with whatever details you want to grab (you don't want to return member_num twice after all). If you want to add a third table, include it in the FROM clause and add another filter on the member_num much like the existing one. It's important to filter the member_num for all tables otherwise you'll potentially get a whole lot of bogus records.

You should also be ensuring that the user input has been sanitised, or better yet bound as a parameter in a parameterised/prepared statement (google those), else it's unsafe.

For outputting the results you can use echo or print (google those as well) to output a string. There are nicer ways if you need to output a very large amount of HTML but I think you'll want to keep it simple for now smile.gif
Edited by randomizer - 4/30/13 at 5:49am
    
CPUMotherboardGraphicsRAM
i7 920 D0 MSI X58 Pro-E GTX 560 Ti 448 3x2GB G.Skill DDR3-1333 9-9-9-24 
Hard DriveHard DriveOptical DriveOS
840 Pro Caviar Black LG BD-ROM Windows 8.1 Pro x64 
MonitorMonitorKeyboardPower
Dell U2713HM Dell U2311H Turbo-Trak (Google it :D) Corsair HX-520 
CaseMouseMouse PadAudio
CM690 Mionix Avior 7000 Everglide Titan AKG K 242 HD 
  hide details  
Reply
    
CPUMotherboardGraphicsRAM
i7 920 D0 MSI X58 Pro-E GTX 560 Ti 448 3x2GB G.Skill DDR3-1333 9-9-9-24 
Hard DriveHard DriveOptical DriveOS
840 Pro Caviar Black LG BD-ROM Windows 8.1 Pro x64 
MonitorMonitorKeyboardPower
Dell U2713HM Dell U2311H Turbo-Trak (Google it :D) Corsair HX-520 
CaseMouseMouse PadAudio
CM690 Mionix Avior 7000 Everglide Titan AKG K 242 HD 
  hide details  
Reply
post #3 of 11
Thread Starter 
ahh , so i can put more tables in the "WHERE " part by putting a coma? ive never seen that :O ill try it out

this is my search smile.gif
Quote:
mysql_connect ("", "","") or die (mysql_error());
mysql_select_db ("");

$term = $_POST;

$sql = mysql_query("select * from customer where CUSTOMER_NUM like '%$term%'");

while ($row = mysql_fetch_array($sql)){
echo 'ID: '.$row;
echo '
Name: '.$row;
echo '
Street: '.$row;
echo '
City: '.$row;
echo '
State: '.$row;
echo '
ZIP: '.$row;
echo '
Balance: '.$row;
echo '
Credit Limit: '.$row;
echo '
Rep Number: '.$row;
echo '

';
}

?>

as you can see , this is a simple PHP script (ok , it didnt pasted right lol but you get the idea), and verry usefull , but what still missing is the search on more tables , it only searches in the "customer" table frown.gif . CUSTOMER_NUM is the Key and $term is the variable that the HTML puts in the search smile.gif

it works . but as i said , it outputs in PHP ... i want it the output in HTML , and the other problem is that if i can do multiple table search in the Query

i dont have a problem with coding/finding the code , what i do have a problem is how is this going to work lol , i was thinking that in the HTML the button links you to another HTML wile activating the script , and another HTML gets the output data , and this is the HTML the first HTML calls , instead of the PHP ...

i still dont know is this is how this is done .. ;_; i dont know what to do lol

EDIT

ok , i got how to display the php result in HTML , its pretty simple biggrin.gif , before the and the second line , after you finish your PHP code , at the end of ?> . close the HTML biggrin.gif that simple ,and last line

ah , and save the file .html instead of .php

now im going to try the multiple table search biggrin.gif wish me luck
Edited by yoi - 4/30/13 at 1:17pm
My Trusty PC
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 1366 950 EVGA Classified 3 EVGA 580 gtx G.skill Pi  
OSKeyboardPowerCase
W7 ultimate 64bit Logitec G510 Corsair  Haf X 
Mouse
Deathadder 
  hide details  
Reply
My Trusty PC
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 1366 950 EVGA Classified 3 EVGA 580 gtx G.skill Pi  
OSKeyboardPowerCase
W7 ultimate 64bit Logitec G510 Corsair  Haf X 
Mouse
Deathadder 
  hide details  
Reply
post #4 of 11
You add multiple tables by separating them with commas in the FROM clause, not the WHERE clause. The WHERE clause contains your filter, and it will usually have multiple conditions separated by AND or OR (depending on whether one or more conditions are mandatory or optional).

The file extension shouldn't be important. In fact it isn't strictly required either, it just depends on how the server is configured. Most will process PHP with a .php or .html extension by default.

Also, if you are always searching based on customer_num, and it is definitely just a number in the database, you should assign the variable $term like this:
Code:
$term = intval($_POST['term']);

This function will try to parse the value as an integer. You can then check if $term equals 0 (which means it failed to parse), and if it did then you can handle that with an error message. The reason for doing this is to ensure that the user entered a valid and safe value. With the current code I could make my search term an SQL query which when formatted correctly will run after your SELECT query. I could drop your customers table just by searching. If the code enforces that I only enter an integer then this can't happen.

Of course if you have a customer with a customer_num of 0, or if your customer_num contains non-numeric characters this will not work, and you will need to do something else smile.gif
Edited by randomizer - 5/1/13 at 2:41am
    
CPUMotherboardGraphicsRAM
i7 920 D0 MSI X58 Pro-E GTX 560 Ti 448 3x2GB G.Skill DDR3-1333 9-9-9-24 
Hard DriveHard DriveOptical DriveOS
840 Pro Caviar Black LG BD-ROM Windows 8.1 Pro x64 
MonitorMonitorKeyboardPower
Dell U2713HM Dell U2311H Turbo-Trak (Google it :D) Corsair HX-520 
CaseMouseMouse PadAudio
CM690 Mionix Avior 7000 Everglide Titan AKG K 242 HD 
  hide details  
Reply
    
CPUMotherboardGraphicsRAM
i7 920 D0 MSI X58 Pro-E GTX 560 Ti 448 3x2GB G.Skill DDR3-1333 9-9-9-24 
Hard DriveHard DriveOptical DriveOS
840 Pro Caviar Black LG BD-ROM Windows 8.1 Pro x64 
MonitorMonitorKeyboardPower
Dell U2713HM Dell U2311H Turbo-Trak (Google it :D) Corsair HX-520 
CaseMouseMouse PadAudio
CM690 Mionix Avior 7000 Everglide Titan AKG K 242 HD 
  hide details  
Reply
post #5 of 11
Thread Starter 
yah , ill post what i have done and what i did , with the help of a friend , and i got another question , ill post it in the upcoming hours smile.gif but it worked , somehow
My Trusty PC
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 1366 950 EVGA Classified 3 EVGA 580 gtx G.skill Pi  
OSKeyboardPowerCase
W7 ultimate 64bit Logitec G510 Corsair  Haf X 
Mouse
Deathadder 
  hide details  
Reply
My Trusty PC
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 1366 950 EVGA Classified 3 EVGA 580 gtx G.skill Pi  
OSKeyboardPowerCase
W7 ultimate 64bit Logitec G510 Corsair  Haf X 
Mouse
Deathadder 
  hide details  
Reply
post #6 of 11
Thread Starter 
ok , a quick update . so far so good biggrin.gif thanks for the help , ive figure it out almost all of the problems I had , thanks Randomizer ..


BUT ... i got another problem ( \ `o')/


this is the code i have that works with a form i have already working (codewise)
Code:
<?php
mysql_connect ("-----------------", "-----------------","--------------")  or die (mysql_error());
mysql_select_db ("-------------");
 
$term = $_POST['term'];
 
$sql = mysql_query("select * from customer where CUSTOMER_NUM like '%$term%'");
 
while ($row = mysql_fetch_array($sql)){
    echo 'ID: '.$row['CUSTOMER_NUM'];
    echo '<br/> Name: '.$row['CUSTOMER_NAME'];
    echo '<br/> Street: '.$row['STREET'];
    echo '<br/> City: '.$row['CITY'];
    echo '<br/> State: '.$row['STATE'];
    echo '<br/> ZIP: '.$row['ZIP'];
   
    echo '<br/><br/>';
    }
 
?>

the problem i have is that , if I open this PHP , without the form imput ( the "term" ) , this .php displays every one on my customer table


...is there a way i could put a IF that tells the PHP that if the $term is empty ( no input from the form ) , dont display all the customer table ? lol

EDIT --

the
Code:
$term = intval($_POST['term']);

doesnt work , somehow ... it displays IDs with 0 on it lol
Edited by yoi - 5/7/13 at 1:33am
My Trusty PC
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 1366 950 EVGA Classified 3 EVGA 580 gtx G.skill Pi  
OSKeyboardPowerCase
W7 ultimate 64bit Logitec G510 Corsair  Haf X 
Mouse
Deathadder 
  hide details  
Reply
My Trusty PC
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 1366 950 EVGA Classified 3 EVGA 580 gtx G.skill Pi  
OSKeyboardPowerCase
W7 ultimate 64bit Logitec G510 Corsair  Haf X 
Mouse
Deathadder 
  hide details  
Reply
post #7 of 11
Quote:
Originally Posted by yoi View Post

...is there a way i could put a IF that tells the PHP that if the $term is empty ( no input from the form ) , dont display all the customer table ? lol

I'm not quite sure what you mean by this. Do you mean that the user hasn't entered anything but just submitted the form?
Quote:
Originally Posted by yoi View Post


doesnt work , somehow ... it displays IDs with 0 on it lol

Yes, that's because you didn't do anything different if it is 0. You're meant to skip the query altogether and print out something else instead (an error message is probably a good idea). In other words:
Code:
if ($term === 0) {
    echo "I can haz valid input?";
}
else {
    // Do the query
}

Remember though that this is only useful if your CUSTOMER_NUM is numeric. If it is not numeric (ie. it contains a mixture of different characters) then you can't do this, because even a real customer number will be treated as invalid. So, is the customer_num numeric? smile.gif
Edited by randomizer - 5/7/13 at 5:36am
    
CPUMotherboardGraphicsRAM
i7 920 D0 MSI X58 Pro-E GTX 560 Ti 448 3x2GB G.Skill DDR3-1333 9-9-9-24 
Hard DriveHard DriveOptical DriveOS
840 Pro Caviar Black LG BD-ROM Windows 8.1 Pro x64 
MonitorMonitorKeyboardPower
Dell U2713HM Dell U2311H Turbo-Trak (Google it :D) Corsair HX-520 
CaseMouseMouse PadAudio
CM690 Mionix Avior 7000 Everglide Titan AKG K 242 HD 
  hide details  
Reply
    
CPUMotherboardGraphicsRAM
i7 920 D0 MSI X58 Pro-E GTX 560 Ti 448 3x2GB G.Skill DDR3-1333 9-9-9-24 
Hard DriveHard DriveOptical DriveOS
840 Pro Caviar Black LG BD-ROM Windows 8.1 Pro x64 
MonitorMonitorKeyboardPower
Dell U2713HM Dell U2311H Turbo-Trak (Google it :D) Corsair HX-520 
CaseMouseMouse PadAudio
CM690 Mionix Avior 7000 Everglide Titan AKG K 242 HD 
  hide details  
Reply
post #8 of 11
Thread Starter 
right now , that column , CUSTOMER_NUM , is SMALLINT

still getting the error http://onthegocrm.vacau.com/searchb.php

the waiting for ID search was my echo biggrin.gif , but as you can see , it displays those 2 guys that have 0's on the ID


EDIT

ohh nvm , it worked , the database seems to update kinda slow lol , lets see how this goes biggrin.gif ill post some stuff at night
Edited by yoi - 5/7/13 at 9:50am
My Trusty PC
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 1366 950 EVGA Classified 3 EVGA 580 gtx G.skill Pi  
OSKeyboardPowerCase
W7 ultimate 64bit Logitec G510 Corsair  Haf X 
Mouse
Deathadder 
  hide details  
Reply
My Trusty PC
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 1366 950 EVGA Classified 3 EVGA 580 gtx G.skill Pi  
OSKeyboardPowerCase
W7 ultimate 64bit Logitec G510 Corsair  Haf X 
Mouse
Deathadder 
  hide details  
Reply
post #9 of 11
Thread Starter 
im back , happy cat dude...


this is what you helped me finish for a masters degree project for a friend of mine lol

http://onthegocrm.vacau.com/

admin / master

and there , you can search different customers . based on a ID

search 148 , 282 ( there are lots more for example purposes but those are the ones i remember right now biggrin.gif )

....

just wanted to give you the thanks for helping me on some questions i had , i can post the codes and all the trickery i had to make to make it work lol
My Trusty PC
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 1366 950 EVGA Classified 3 EVGA 580 gtx G.skill Pi  
OSKeyboardPowerCase
W7 ultimate 64bit Logitec G510 Corsair  Haf X 
Mouse
Deathadder 
  hide details  
Reply
My Trusty PC
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 1366 950 EVGA Classified 3 EVGA 580 gtx G.skill Pi  
OSKeyboardPowerCase
W7 ultimate 64bit Logitec G510 Corsair  Haf X 
Mouse
Deathadder 
  hide details  
Reply
post #10 of 11
Happy to help smile.gif
    
CPUMotherboardGraphicsRAM
i7 920 D0 MSI X58 Pro-E GTX 560 Ti 448 3x2GB G.Skill DDR3-1333 9-9-9-24 
Hard DriveHard DriveOptical DriveOS
840 Pro Caviar Black LG BD-ROM Windows 8.1 Pro x64 
MonitorMonitorKeyboardPower
Dell U2713HM Dell U2311H Turbo-Trak (Google it :D) Corsair HX-520 
CaseMouseMouse PadAudio
CM690 Mionix Avior 7000 Everglide Titan AKG K 242 HD 
  hide details  
Reply
    
CPUMotherboardGraphicsRAM
i7 920 D0 MSI X58 Pro-E GTX 560 Ti 448 3x2GB G.Skill DDR3-1333 9-9-9-24 
Hard DriveHard DriveOptical DriveOS
840 Pro Caviar Black LG BD-ROM Windows 8.1 Pro x64 
MonitorMonitorKeyboardPower
Dell U2713HM Dell U2311H Turbo-Trak (Google it :D) Corsair HX-520 
CaseMouseMouse PadAudio
CM690 Mionix Avior 7000 Everglide Titan AKG K 242 HD 
  hide details  
Reply
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: Coding and Programming
Overclock.net › Forums › Software, Programming and Coding › Coding and Programming › HTML PHP and MySQL help !