[SOLVED] scripting a job database

[garethhall]

I don't know how well you are with database design but if you are no to good at it yet and it sounds that way (sorry) look into relational database design this will help you alot. Start by writing a list for all the data you need to store. Next try and figure the relationships between that data meaning 1 to 1 relation and 1 to many relations. You most probably will need a connecting table to which will add alot on complexity to you php. I hope this helps a bit.

[xtopolis]

You could use sessions or $_GET values to do it.  I can't look at it in detail atm, but perhaps

CV's tutorial http://www.phpfreaks.com/tutorial/basic-pagination will hold the answers.

[rilana]

thank you, I did the whole pagintion with this tutorial, it was realy helpfull. But he starts with a clear easy connection which is allways the same.... I will try to figure it out with session.... thank you

[rilana]

cant figure it out, dont think it would work by sending the value in a link, I tryed putting this in the browser, http://www.smartpersonal.ch/stellenResult.php?currentpage=2&region=$seeLinks and it just ignores it and shows all the data, I guess becuase i didn't declear anywhere what to do when region==seeLinks...??? I realy would apreciate any help, I had to put the website online now... and have to go to work now. Hopefully nobody will notice it.....

thank you so much for everything.

 

Rilana

[xtopolis]

I think you need to reorder everything.  Go back to your pages and take out all the things that aren't needed or are no longer necessary for your current scheme.  You changed your tables around etc so you need to make sure to fix things.

 

What I'm saying is, you've made changes to a lot, time to go back and clean up.  Get it working with displaying all stuffs, then re add the pagination based on your new methods.  You will need to look for the get vars etc.  I can't help much more because I don't know your code, even if you pasted the source, it's been built by your manner of thinking.

 

Just relax, go back and clean up everything, even try copy / pasting into a new page.  ! But don't overwrite your current stuff until you get the new stuff working!

[rilana]

Thank you, but this is still the old version of the database strukture. I had to put it online and will work on the backend and the new structure while it's online.

 

I have an idea I think how I could solve this, actually I guess I have a lot of ideas put dont really acomplish anything....

 

When I do this:

$regions = join("', '", $_POST['region']);
echo "$regions";

it gives me the right output of my checkboxes. but when I go forther and try to send it in a link like this:

   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage&anfrage=$regions'>></a> ";

it only gives the first value back.... for example it should give back this:

http://www.smartpersonal.ch/stellenResult.php?currentpage=2&anfrage='seeRechts', 'alle' but it gives me back this: http://www.smartpersonal.ch/stellenResult.php?currentpage=2&anfrage=seeRechts and then it says

Warning: join() [function.join.php]: Invalid arguments passed in stellenResult.php on line 85 and line 85 is this:

$regions = join("', '", $_POST['region']);

 

Now my plane was to take all the values out of the form, but it in a variable, send the variable with the link and then do an if statement over again and define $whereclause.

 

Like above but without on submit...

 

if (isset($_POST['btnSubmit']))

{

 

    $where = array();

    $whereclause = '';

 

    if (isset($_POST['region']))

    {

        $regions = join("', '", $_POST['region']);

        $where[] = "(region IN ('$regions'))" ;

    }

    if (isset($_POST['beruf']))

    {

        $berufe = join("', '", $_POST['beruf']);

        $where[] = "(beruf IN ('$berufe'))" ;

    }

if (isset($_POST['anstellung']))

    {

        $anstellungen = join("', '", $_POST['anstellung']);

        $where[] = "(anstellung IN ('$anstellungen'))" ;

    }

if  (count($where) > 0) $whereclause = ' WHERE ' . join (' AND ', $where);

 

}

 

it seems easy but I have problems getting it to work for me. Can you help me? Thank you so much.

 

[xtopolis]

You could try using a different way to join:

$regions = join("|", $_POST['region']);

But you may want to add a urlencode incase your stuff has spaces.

 

But using a pipe symbol | will be better since ' <- single quotes aren't valid in urls!

[rilana]

Thank you so much for your help. I finaly got a solution with sessions. Seems easy now. :-)

 

What do you think?

if (isset($_POST['btnSubmit']))
{

    $where = array();
    $whereclause = '';
    
    session_start();
    session_destroy();
    
    if (isset($_POST['region']))
    {
        $regions = join("', '", $_POST['region']);
        session_start();
    $_SESSION['regions'] = join("', '", $_POST['region']);
    }
    if (isset($_POST['beruf']))
    {
        $berufe = join("', '", $_POST['beruf']);
        session_start();
    $_SESSION['berufe'] = join("', '", $_POST['beruf']);
    }
    if  (count($where) > 0) $whereclause = ' WHERE ' . join (' AND ', $where);
    
}

session_start();  
if(isset($_SESSION['regions']))
{
    $region = $_SESSION['regions'];
    $where[] = "(region IN ('$regions'))" ;
     }
if(isset($_SESSION['berufe']))
{
    $beruf = $_SESSION['berufe'];
    $where[] = "(beruf IN ('$berufe'))" ;
}
if  (count($where) > 0) $whereclause = ' WHERE ' . join (' AND ', $where);


$sql = "SELECT * FROM jobs $whereclause order by datumsanzeige = 'fake' DESC, datum DESC";  

 

Thank you for beeing there, Rilana

[xtopolis]

Glad you found a solution, hope it continues to work well.  Your welcome; you did all the work!

[rilana]

Hy guyes. I am on my way scripting the frontend with join and different tables. So far it went along pretty good. But as allways there is something that want work for me. As soon as I ad an order by, it screws up.

 

$sql = "SELECT
		*
	FROM 
		stelle 
	JOIN 
		stelle_region
	ON
		stelle_region.stelleID=stelle.stelleID
	JOIN
		region
	ON
		region.regionID=stelle_region.regionID
	JOIN 
		stelle_beruf
	ON
		stelle_beruf.stelleID=stelle.stelleID
	JOIN
		beruf
	ON
		beruf.berufID=stelle_beruf.berufID

	$whereclause order by stelleDatumsanzeige = 'fake' DESC, datum DESC"; 

 

The "stelleDatumsanzeige" field is in the table "stelle". I dont get it, what do I need to do to make this one work? do I have to join stelleDatumsanzeige? I also tryed "stelle.stelleDatumsanzeige" but that want work eighter...

 

I would apreciate your help, thank you, Rilana

[xtopolis]

You can't order by "column = 'something' afaik.  Are you trying to order by a value first?  That would be something different.

 

Also you can shorten your query if I'm not mistaken.

$sql = "SELECT * FROM stelle s
        JOIN stelle_region sr USING (stelleID)
        JOIN region r USING (regionID)
        JOIN stelle_beruf sb USING (stelle ID)
        JOIN beruf b USING (berufID)";
        
$whereclause = " ORDER BY stelleDatumsanzeige DESC, datum DESC";

You may need to reorder your JOINs, and the whereclause will need table identifier im thinking

(like s.stelleDatumsanzeige if stelleDatumsanzeige belonged to the table stelle)

[rilana]

Hy, I got the ordering solved, it was a stupied mistake... it looks like this now.

 

$whereclause order by stelle.stelleDatumsanzeige = 'fake' DESC, stelle.stelleDatum DESC

it works fine.

 

But now I am working on the backend and I am running into trubles. I am having checkboxes for beruf like this

<input type="checkbox" name="beruf[]" value="alle" />Alle Berufe<br />
          <input type="checkbox" name="beruf[]" value="tech" />Technische Berufe<br />
          <input type="checkbox" name="beruf[]" value="bau" />Bau<br />
          <input type="checkbox" name="beruf[]" value="verkauf" />Verkauf<br />

 

And I have to put this values into my table now that looks like this

CREATE TABLE IF NOT EXISTS `stelle_beruf` (
  `stelleID` int(11) NOT NULL default '0',
  `berufID` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


INSERT INTO `stelle_beruf` (`stelleID`, `berufID`) VALUES
(108, 1),
(108, 2),

 

1 is tech

2 is kauf

 

Now the difficulty I am having is how can I say get the checkbox values and if there is more then one box checked then insert 2 rows into mysql.... Because if there is 2 boxes checked, I need to have to entrys into stelle_beruf with the same stelleId but different berufId. How would I do something like that?

 

Thank you for your help, Rilana

[xtopolis]

Here is an example, if I am understanding your question correctly.  Copy and paste this code/ try it in a browser, then look at the source.

 

<html><head></head>
<body>

<form method="POST">
  <input type="checkbox" name="beruf[]" value="4" alt="alle" />Alle Berufe<br />
  <input type="checkbox" name="beruf[]" value="1" alt="tech" />Technische Berufe<br />
  <input type="checkbox" name="beruf[]" value="3" alt="bau" />Bau<br />
  <input type="checkbox" name="beruf[]" value="2" alt="verkauf" />Verkauf<br />
<input type="submit" value="Create" name="createListing" />


<?php

if(isset($_POST['createListing'])  && count($_POST['beruf'])){
echo '<hr /><br />';


foreach($_POST['beruf'] as $b){
  echo "You have selected: $b".'<br />';
}

//MYSQL GENERATE
echo '<br />';

$stelleID = 108;// you could use mysql_insert_id()
echo '<textarea rows="10" cols="90">';

$sql = "INSERT INTO `stelle_beruf` (`stelleID`, `berufID`) VALUES ";

$c = count($_POST['beruf']);
if($c > 1){

  $i=1;
  foreach($_POST['beruf'] as $b){
  $comma = ($i < $c) ? ',' : '';//add a coma to the end, but not the last one

    $sql .= "($stelleID,$b)$comma";
    $i++;
  }

  $sql .= '';

}else{

  $sql .= "($stelleID, $b)";

}

echo $sql;


echo '</textarea>';
}//create listing set
?>
</body>
</html>

 

If your stelleID comes from a previous query, you can get it with: mysql_insert_id

You could probably clean that code up quite a bit.

 

Also, you will notice I changed your values in your inputs to numbers and added the alt attribute with the names...  You could use the names .. but then you would have to look up their ID values which is a waste of time.  If there is another reason you wish to have the value=name, then I suggest you make it something like value="id|name" and split it on | later so that way you can still have the id right away.  Also, numbers are more efficient that letters when searching/comparing.

 

If you have more questions , post them of course -- but I have gotten busy with my classes so I may not be able to help you.  This just means you may want to post your questions as a new topic so that people aren't scared off by the length of this thread when trying to help you.

[rilana]

thank you, that helped a lot. I have one more question about dynamic checkboxes... but will post a new thread like you sugested :-)

 

Thank you so much for all your help. Rilana

[rilana]

Thank you! I am done with the project and I learned a lot! Thank you all!!! I couldn't have done it without you!

Thank you, Rilana