[SOLVED] scripting a job database

[rilana]

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

[Barand]

One region field. eg.

 

SELECT ... WHERE region IN (1,3,6)

[.josh]

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?

[Barand]

I thought I was beyond "Hello World", but hey, still learning

[rilana]

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....)

[revraz]

lol

 

I thought I was beyond "Hello World", but hey, still learning

[.josh]

I thought I was beyond "Hello World", but hey, still learning

 

Don't worry, young grasshopper, practice hard, and one day you shall achieve zen...d

[rilana]

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

[Barand]

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.

[xtopolis]

Barand, how do you get the output like that?

Regarding your tables enclosed in pipes and plus signs..

[corbin]

MySQL command line tool.

 

 

mysql under linux or mysql.exe under windows.

[xtopolis]

Ah, I'm spoiled nowadays, only using PHPmyadmin... I remember now seeing this back in the day when I first learned some mysql basics.  Thanks.

[rilana]

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.

[rilana]

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

[Barand]

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>

[rilana]

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

 

 

 

 

 

 

 

 

[rilana]

OK I got it I think! I added this line

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

[Barand]

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.

 

[rilana]

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

[Barand]

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>

[rilana]

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?

 

 

[Barand]

 

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

 

 

[Barand]

... and SQL

[rilana]

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.

 

[Barand]

Is there a logical reason why this

while($row = mysql_fetch_array( $result ))

doesn't work no more?

 

need to see the query code to answer that.

 

Have you tried checking mysql_error()?

 

Or maybe the query isn't finding any matching rows.