Jump to content

[SOLVED] scripting a job database


rilana

Recommended Posts

Hi all I am trying to start from scratch. I wanna script a Job database. Frontend with multiple options of choosing from regions, jobfield and jobtypes. And Backend which makes it possible for anyone to add jobs. My first confusion is the jobsearch. Lets say I have a form with chekboxes for 6 regions. And someone checks region1 and region2, how do I say go to the database and show all entries with region1 and region2. I am so confused that I am not evan shure if I should set up the database with only one region field, or do I need 6 region fields? What do I need to learn to figure this out? Any Input would help. thank you verry much, Rilana

Link to comment
Share on other sites

  • Replies 89
  • Created
  • Last Reply

Top Posters In This Topic

I don't know how versed you are with php/sql but based off your post you can't be much farther than the starter "hello world" tutorial... I think you're putting the cart before the horse.  You need to learn how to do basic stuff before jumping into a big project like that.  I mean, I can point you to a basic database handling tutorial, but are you even gonna understand that much?

Link to comment
Share on other sites

yes I am behond hello world. But I never learned things the normal way from a to z, so I guess sometimes I have huge gabes.... I am confused how to evan structure this. And I dont wanna use a precoded thing because I wanna learn and I dont want unnecessairy code... (sorry dont know how to spell english eighter....)

Link to comment
Share on other sites

thanks for all the feedback.... I am feeling so frustrated because I am not getting anywhere. Thanks Barand I know what you mean with One region field. eg. SELECT ... WHERE region IN (1,3,6) at least now I know what I have to search for and learn. I tryed to find a tutorial on the web about inserting checkbox values into mysql. But couldn't find anything good. Does by any chance anyone know of one? Maby I will be smarter tommorow, but for now I am going to sleep. Thanks so much guyes, wouldn't know how to do anything with you.... :-) Rilana

Link to comment
Share on other sites

Example

[pre]mysql> SELECT * FROM job;

+-------+------------------------+--------+

| idjob | jobtitle              | region |

+-------+------------------------+--------+

|    1 | Architect              |      1 |

|    2 | Bricklayer            |      2 |

|    3 | Carpenter              |      1 |

|    4 | Doctor                |      3 |

|    5 | Engraver              |      4 |

|    6 | Farmer                |      2 |

|    7 | Gynaecologist          |      5 |

|    8 | Hospital administrator |      6 |

+-------+------------------------+--------+

 

mysql> SELECT * FROM `job`

    -> WHERE region IN (1,2)

    -> ORDER BY `region`;

+-------+------------+--------+

| idjob | jobtitle  | region |

+-------+------------+--------+

|    1 | Architect  |      1 |

|    3 | Carpenter  |      1 |

|    2 | Bricklayer |      2 |

|    6 | Farmer    |      2 |

+-------+------------+--------+

[/pre]

 

Regarding checkboxes,

 

When inserting a new job it will only have one region/location so a simple dropdown, or radio buttons, would be a better choice.

 

However, when searching, the user will want to select one or more regions so it is at this point the checkboxes are more suitable.

Link to comment
Share on other sites

Oh I was realy thinking wrong yesterday, I always was trying to search for a way to insert more then one region in my sql, but that would be so wrong! I dont have to insert them, I only have to check them and ask for the right output... thank you Barand... I will try to do this tonite! thanks a lot.

Link to comment
Share on other sites

Hy guyes I tryed the following

$reg = implode(" ", $region);
echo "$reg";

that showed me that the chekboxes work. And then I tryed

$result = mysql_query("SELECT * FROM jobs where region IN ('zurich')") 

And this is where I am stuck. If I try to put $region unstead of zurich, it want work anymore and if I put zurich,glarus (more then one) i doesn't work anymore eighter. Can anyone give me a hint please? Thanks a lot, Rilana

Link to comment
Share on other sites

try

<?php
if (isset($_POST['region']))
{
    $regions = implode ("', '", $_POST['region']);
    $sql = "SELECT * FROM jobs WHERE region IN ('$regions')";
    echo $sql;
}
?>
<form method="POST">
Region A <input type='checkbox' name='region[]' value='A'><br />
Region B <input type='checkbox' name='region[]' value='B'><br />
Region C <input type='checkbox' name='region[]' value='C'><br />
<input type='submit' name='btnSubmit' value='Submit'>
</form>

Link to comment
Share on other sites

Hy thank you. the

if (isset($_POST['region']))
{
    $regions = implode ("', '", $_POST['region']);
    $sql = "SELECT * FROM jobs WHERE region IN ('$regions')";
    echo $sql;

works I think, it prints SELECT * FROM jobs WHERE region IN ('zurich', 'glarus')

 

But the rest that used to work does not work anymore...

echo "<table border='1'>";
while($row = mysql_fetch_array($sql)) {
echo "<tr><td>"; 
echo $row['id'];
echo "</td></tr>"; 
} 
echo "</table>";
}

it gives this error: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/smartper/public_html/db/search.php on line 21

 

 

 

 

 

 

 

 

Link to comment
Share on other sites

Yes, that's the missing link.

 

 

It's more efficient to work with IDs rather than region names, so have a region table

[pre]id | region

---+-----------

1 | Zurich

2 | Glarus

etc[/pre]

 

and store the region id in the jobs table.

 

Having the region table also makes it easy to generate the search choices.

 

Link to comment
Share on other sites

Hi I am not shure if I understand you correctly, I do have an ID table. But I think you meant unstead of saying the region is zurich or glarus, say the region is 1 or 2... is that what you meant?

 

I also tryed to make a multiple selection now, but of course it does not work. Can you tell me if this is evan possible somehow?

 

if (isset($_POST['region']))
{
    $regions = implode ("', '", $_POST['region']);
    $sql = "SELECT * FROM jobs WHERE region IN ('$regions')";
} else if (isset($_POST['region'], $_POST['beruf'] )) 
$regBeruf = implode ("', '", $_POST['beruf'], $_POST['region']);
    $sql = "SELECT * FROM jobs WHERE region, beruf IN ('$regBeruf')";
{

$result = mysql_query($sql,$con);

 

thank you

Link to comment
Share on other sites

Hi I am not shure if I understand you correctly, I do have an ID table. But I think you meant unstead of saying the region is zurich or glarus, say the region is 1 or 2... is that what you meant?

 

Yes

 

I also tryed to make a multiple selection now, but of course it does not work. Can you tell me if this is evan possible somehow?

 

if (isset($_POST['region']))
{
    $regions = implode ("', '", $_POST['region']);
    $sql = "SELECT * FROM jobs WHERE region IN ('$regions')";
} else if (isset($_POST['region'], $_POST['beruf'] )) 
$regBeruf = implode ("', '", $_POST['beruf'], $_POST['region']);
    $sql = "SELECT * FROM jobs WHERE region, beruf IN ('$regBeruf')";
{

$result = mysql_query($sql,$con);

 

thank you

 

I'm not sure exactly what you are trying to do here. Do you mean

 

<?php
if (isset($_POST['btnSubmit']))
{
    $where = array();
    $whereclause = '';
    
    if (isset($_POST['region']))
    {
        $regions = join(', ', $_POST['region']);
        $where[] = "(region IN ($regions))" ;
    }
    if (isset($_POST['beruf']))
    {
        $berufen = join(', ', $_POST['beruf']);
        $where[] = "(beruf IN ($berufen))" ;
    }
    
    if  (count($where) > 0) $whereclause = ' WHERE ' . join (' AND ', $where);
    
    $sql = "SELECT * FROM jobs $whereclause";
    
    echo $sql;
}
?>
<form method="POST">
<h3>Region</h3>
Zurich <input type='checkbox' name='region[]' value='1'><br />
Glarus <input type='checkbox' name='region[]' value='2'><br />
Berne <input type='checkbox' name='region[]' value='3'><br />
<h3>Beruf</h3>
Developer <input type='checkbox' name='beruf[]' value='1'><br />
Architect <input type='checkbox' name='beruf[]' value='2'><br />
Doctor C <input type='checkbox' name='beruf[]' value='3'><br />

<input type='submit' name='btnSubmit' value='Submit'>
</form>

Link to comment
Share on other sites

wow thats realy dynamic, I like it! And I think I understand it more or less...

May I ask you, $where = array(); is this like saying get all the arrays?

 

join(', ', $_POST['region']); what does ',', mean?

 

the echo $sql works, but there seems to be a problem now with

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

just like last time, but this time I still have decleared

 $result = mysql_query($sql,$con);

 

Thanks you so much for your help. Can I ask you, did you go to a programming school or did you teach yourself?

 

 

Link to comment
Share on other sites

 

May I ask you, $where = array(); is this like saying get all the arrays?

 

It defines $where as an empty array.

 

 

join(', ', $_POST['region']); what does ',', mean?

 

create a string with ',' between each array element

 

the echo $sql works,

That was there just so you can see the structure of the resulting query

 

 

Thanks you so much for your help. Can I ask you, did you go to a programming school or did you teach yourself?

 

I had a 5-day course in a language called NICOL in 1970. Since then self taught BCPL, C, C++, Java, Pascal, PHP, Postscript, VB

 

 

Link to comment
Share on other sites

hm 1970 I wasn't even arround yet... sounds pretty interesting.

 

Yes I know the echo $sql is only there to see whats going on. And I like to see whats going on. :-)

 

Is there a logical reason why this

while($row = mysql_fetch_array( $result ))

doesn't work no more?

 

I am going to sleep now. Its almost 2 a.m. Maby I will be refreshed tommorow and go on with my learning..

 

Thank you and goodnight.

 

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.


×
×
  • 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.